Monday, May 6, 2013

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