Hi,

In using the indirect formula

=IF((INDIRECT($E5&"!$C$167"))=0,"",INDIRECT($E5&"! $C$167"))

wher E5 is the name of the worksheet, when I name the worksheet say for eg
ask 0708, I get a reference problem. But when I rename it ask0708 which has
no spaces, it accepts it. I even tried it like this ask_0708 but still got
the reference problem.

Can you please tell me why this happens.

Thanks

You have to wrap the sheet name in single quotes.

Something like:
INDIRECT("'"&$E5&"'!$C$167")

Note inside the (, it is [double quote][single quote][double quote] and
then after the 2nd & it is [double quote][single quote]!C$167".

Scott

sanmos wrote:
Hi,

In using the indirect formula

=IF((INDIRECT($E5&"!$C$167"))=0,"",INDIRECT($E5&"! $C$167"))

wher E5 is the name of the worksheet, when I name the worksheet say for eg
ask 0708, I get a reference problem. But when I rename it ask0708 which has
no spaces, it accepts it. I even tried it like this ask_0708 but still got
the reference problem.

Can you please tell me why this happens.

Thanks

Thanks

"Scott" wrote:

You have to wrap the sheet name in single quotes.

Something like:
INDIRECT("'"&$E5&"'!$C$167")

Note inside the (, it is [double quote][single quote][double quote] and
then after the 2nd & it is [double quote][single quote]!C$167".

Scott

sanmos wrote:
Hi,

In using the indirect formula

=IF((INDIRECT($E5&"!$C$167"))=0,"",INDIRECT($E5&"! $C$167"))

wher E5 is the name of the worksheet, when I name the worksheet say for eg
ask 0708, I get a reference problem. But when I rename it ask0708 which has
no spaces, it accepts it. I even tried it like this ask_0708 but still got
the reference problem.

Can you please tell me why this happens.

Thanks