INDIRECT-fuction 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

 

[sandved]

Add a single qoute around the sheet name like this:
=INDIRECT("'"&B1&"'!"&C1;1)

Regards
Nikolai Sandved
http://www.pvv.org/~nsaa/excel.html