Function "Indirect"

[Indriano]

od morning, I am Italian. I have a problem with Excel , if you have time
to answer to me:

I have to convert a great sheet with direct formulas to an Indirect one
like that:


1 a b c d e
2 1
3 1
4 1
5 1
6 = sum (A1:A5)


= sum(indirect("a1"):indirect("a5")).

My problem is to avoid that Cut-Paste could cancel the "father formulas".
the only solution that I know is to use the function Indirect.


The problem is that I have to copy formulas in thousands of formulas and
I lost the new formulas.
It became not for example:

= sum(indirect("B1"):indirect("B5")).



(It became not
= sum(indirect("B1"):indirect("B5")) for example.




Furter question:
Is it possible to avoid that the operator open further workbook ?



Thank You.


Indriano

 

[AltaEgo]

Indriano

Would the use of fixed references overcome your problem?

For example, instead of using = sum(indirect("a1"):indirect("a5")). to
replace = sum (A1:A5), can you use = sum ($A$1:$A$5)?


To open workbooks one at a time and automatically perform functions then
close workbooks:
http://www.mrexcel.com/archive/VBA/24086.html

To have a batch of files open each time you start Excel:
enter a directory path in Tools, Options General TAB "At startup, open all
files in".