Monday, May 6, 2013

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)

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