Indirect function #ref problem

[hugolm]

Hi everyone!

I have a probleme with a indirect function. I am using it to catch some information in other sheets. It works well for my first 4 sheets but I get a ref error for the next 4 ones. I do not understant. I target the same cell in each sheet. for example here is the formula. INDIRECT(C19&"!i21") where cell c19 is the name of the target sheet. The problem may come from the name of the sheet whith have a space like "Head Office". I think it also could come from the fact that I delete this sheet and create a new one under the same name.
Do you have any idea. I am getting crazy.

Thanks for your help,


Hugo

[JosephP]

if you have spaces or special names you must use quotes
INDIRECT("'"&C19&"'!i21")

[Richard Buttrey]

Hi,

Yes almost certainly the space is the problem. If you have a space in a sheet name then you need to include the single ' symbol. e.g.

INDIRECT("'"&C19&"'!i21")

In fact it's a good idea to always include it whether the sheet name has a space or not.

[hugolm]

Thank you so much JP and Richard! I works perfectly. I did not know this trick.

Thanks again

Hugo