Hi,

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

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

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

Can anybody help please???????


--
Ayrton
------------------------------------------------------------------------
Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438
View this thread: http://www.excelforum.com/showthread...hreadid=491471

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


--

Regards,

Peo Sjoblom

"Ayrton" wrote in
message ...

Hi,

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

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

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

Can anybody help please???????


--
Ayrton
------------------------------------------------------------------------
Ayrton's Profile:
http://www.excelforum.com/member.php...o&userid=29438
View this thread: http://www.excelforum.com/showthread...hreadid=491471

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


--
Ayrton
------------------------------------------------------------------------
Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438
View this thread: http://www.excelforum.com/showthread...hreadid=491471

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


--

Regards,

Peo Sjoblom



"Ayrton" wrote in
message ...

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


--
Ayrton
------------------------------------------------------------------------
Ayrton's Profile:
http://www.excelforum.com/member.php...o&userid=29438
View this thread: http://www.excelforum.com/showthread...hreadid=491471

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


On Wed, 7 Dec 2005 09:34:42 -0600, Ayrton
wrote:


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

Thanks again


--
Ayrton
------------------------------------------------------------------------
Ayrton's Profile: http://www.excelforum.com/member.php...o&userid=29438
View this thread: http://www.excelforum.com/showthread...hreadid=491471