yogi_Use INDIRECT Function When Sheet Name Contains Special Characters Whether Sheet Name Is Enclosed In Single Quotes Or Not

                                          Google Spreadsheet   Post  #1171
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 06, 2013
user Stephane Brodu (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/e-7QyQcd-Ec)
INDIRECT function does not work the same way when worksheet name contains special chars. Bug or misunderstand ?
OS : Win 7 64 bits

Browser : Chrome Version 26.0.1410.64 m  (french)

When the name of the sheet is passed via a cell content to the INDIRECT function (AKA : INDIRECT (AnyCell & "!MyRange") ) : 

• INDIRECT needs single quote around AnyCell when the sheet name contains special chars (like - or accented chars)
• INDIRECT ("'" & AnyCell & "'!MyRange")           will work if AnyCell = Juan-Carlos  (or Sébastien)  and will NOT work if AnyCell = Betty
• INDIRECT (AnyCell & "!MyRange")                   will NOT work if AnyCell = Juan-Carlos (or Sébastien)  and WILL work if AnyCell = Betty

Is it me or it is a bug ?

Thanks a lot for all your great work & support ! 

StefBrodu

-----------------------------------------------------------------------------------------------------------------

I think INDIRECT function works fine for sheet named that contain special characters whether the sheet name is enclosed in single quotes or not ... see the following illustration