Convert text to formula - Help pleaaase

[Ayrton]

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???????

[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


--

Regards,

Peo Sjoblom

"Ayrton" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--

Regards,

Peo Sjoblom



"Ayrton" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


On Wed, 7 Dec 2005 09:34:42 -0600, Ayrton
<[email protected]> 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

[Ayrton]

Thanks again