INDIRECT FUNCTION jams?
 

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.

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

" wrote:

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.

Thanks BJ!

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

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.

" wrote:

Thanks BJ!

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