Convert text to formula - Help pleaaase

[Ayrton]

Hi,

I have a formula such as "=sum(Hello!A1:B2)", where "Hello" is
spreadsheet. I have a column with all the names of my spredsheets and
need to use the same set of formula referencing to all of them, one b
one. I need to, either:

Replace the name of the preadsheet by a cell reference so I can use th
same fomula to call various spreadsheet.

or find a way to convert a text into a formula - to do the above, I ca
use the "SUBSTITUTE" function into my formula but this is then a tex
and I cannot find the way to evaluate it (The INDIRECT function doe
not work...)

Can anybody help please??????

 

[Peo Sjoblom]

INDIRECT does work as long as the workbook with the sheets in question is
open

=SUM(INDIRECT("'"&A1&"'!A1:B2"))

with the sheet name in A1

 

[Ayrton]

Thanks a lot. your "grammar" does work but I am not sure why the "'" ...
are for? I cannot find this example in the help...

Anyways, thanks again

 

[Peo Sjoblom]

The "'" at the start is a way of making sure it will work if you have sheet
names with spaces in them since excel automatically precede those with an
apostrophe (I assume you don't have sheet named Hello) by adding this you
can use whatever sheet name you want. The last pair "'!A1:B2" is because
INDIRECT needs them to make that string converted to a usable value. e.g.

=INDIRECT(A1) will not return what's in A1, INDIRECT("A1")
will

If A1 holds a reference like A2 then

=INDIRECT(A1)

will return what's in A2

 

[Gord Dibben]

Ayrton

The "'" places ' around the sheet name.

This is rquired if the sheet name has space(s) in it.

='sheet one'!A16 requires the ' ' around the name.

=Sheet2!A13 does not require ' '


Gord Dibben Excel MVP

 

[Ayrton]

Thanks agai