yogi_WorkAround For Working With Customized Range Names Using Sheet Names With Special (Non-Alphabetical) Characters

                                          Google Spreadsheet   Post  #1173
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)
---
Hi Yogi ! 

Thanks a lot for your quick answer and the time you spent on it ! 

I tried your sample and it worked...

Actually I realized that my formula is far more complex than the one I gave and that the problem is elsewhere...

In fact, I was using INDIRECT with a CUSTOMIZED range name, doing a sum on it... in an array formula.  Looks something like : 

=ArrayFormula(SUM(indirect("'"&A7&"'"&"!MyDataSet")))

or

=ArrayFormula(SUM(indirect(A7&"!MyDataSet")))

In this case only, I reproduce the error initially reported.

(Note that the ArrayFormula does not affect this behavior)

I publicly shared a file with this example at : https://docs.google.com/spreadsheet/ccc?key=0As9bECaBuVLMdGdTcVBTeUNDYkF6RGM4ZEQxTU9JZnc#gid=0

Hope you'll be able to find something ! 

StefBrodu

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

Yes Indeed ... there is a problem with using customized RangeNames using special (non-alphabetical) characcters; so have a look at the WorkAround solution I have presented in the following