INDIRECT FUNCTION jams?

[SIGE_GOEVAERTS]

Hello There,


I get a REF-error sometimes with the following function:
=INDIRECT(B1&"!"&C1;1)


Imagine:
in B1: Sheet1
In C1: A1


Will give me the value in Sheet1!A1. Perfect!


Suppose now, I make a copy of Sheet1 => Sheet1 (2)
Using the Indirect function with in B1 : Sheet1 (2) generates me a REF
error!


In Short: I cannot use: Blank Space, ( , &, %, or sortalike symbols ...

to indicate my sheetname (nevertheless a sheetname can contain these
symbols)


How can I overcome this?


Which wizard has a solution to overcome this...,
Sige

(I have posted this thread also in
microsoft.public.excel.worksheetfunctions (mind the missing dot)sorry
for that) Don't know which NG is active.

 

[Guest]

change to
=INDIRECT("'"&B1&"'!"&C1;1)
you need single quote marks around sheet names with spaces

 

[SIGE_GOEVAERTS]

Thanks BJ!

Very simple ...but you have to know it!
Thx a million!
Sige

 

[Guest]

I was tearing what little hair I have out because of this problem a couple of
years ago when somone pointed it out to me.