Changing part of an array in Excel

[DuckBill]

I have developed a large cost estimating spreadsheet for my company and am always looking for ways to reduce the size of the file it creates so that it is easier to e-mail etc. I have been looking into using array formulas on the spreadsheet itself and I believe that their use would help cut down the file size quite a lot.

However, I need to be able to insert and delete rows in the spreadsheet, for which I have a couple of macros (to remove protection, insert the rows, insert appropriate formulae, re-apply protection, etc.). And of course when I try and run these on a new version using array formulas, I am told that "You cannot change part of an array".

Does anyone have any ideas on how to get around this?

Thanks in advance.

 

[MichaelRed]

Use a REAL (or at least quasi REAL) database and export the 'Spreadsheet' as necessary.



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[DuckBill]

Thanks for the response, MichaelRed, but I'm not sure that I fully understand it.
If you are suggesting that we should have created the whole thing in a database in the first place, then that's possibly a fair comment. However, this estimating system is not used to retain any cost data; the company produces one-off designs and so the estimating process effectively starts from scratch each time, but based on some standard man-hour rates, etc. The spreadsheet was set up so that it could be used by engineers working on proposals. They need to be able to modify it to suit their requirements and we found that a spreadsheet was inherently more "user-friendly" than a database. Sure, you can make a database fairly bullet-proof, but we don't have the in-house resources to do that, nor to keep modifying such a system as would inevitably be required. (We have had to continously adapt and enhance the spreadsheet over the last five years, so I don't suppose a database would be any different!)
If, however, you are suggesting a way of solving my problem with the spreadsheet by temporarily exporting it to a database...then you've lost me! Could you explain please?

Thanks.

DuckBill

 

[MichaelRed]

What I was trying to suggest is the generation of a "Master" estimating guide in the DB. Elements from the Master (guide) would be combined to generate a specific estimate sheet from the master. It (the specific) could then be a spreadsheet. From your response, this does not appear to be a practical soloution for you. Sorry about the mis-direction.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[DuckBill]

Thanks for the clarification. That could be a useful approach, perhaps for other purposes if not this one.

Anyone got any ideas on the original problem? (Inserting rows in a spreadsheet with array formulas, see above)

DuckBill