Tuesday, May 7, 2013

yogi_WorkAround (Sort Of) For Using A Dynamic Range In Google Spreadsheet

                                          Google Spreadsheet   Post  #1175
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 07, 2013
www.energyefficientbuild.com.   May 07, 2013
user Fischlr (http://productforums.google.com/forum/?zx=f30enu3qruz8#!category-topic/docs/spreadsheets/ng83v1vtdjU)
Dynamic Named Ranges
I did some research and found a relevant thread... From 2011.

Is there a way to do the following with current Sheets?

I'm trying to create a Dynamic-Named-Range. I have 5 columns that I'm using in a Pivot Table.  The formula I'm using is =offset(sheet1!A1,0,0,Count(sheet1!A:A)+1,1)
The formula works in the actual worksheet, but is no good as a named range.
I have used this 1000's of times in Excel, no problem.  Is there still a limitation on Google Spreadsheets?
as of now (May-07-2013) one can not use formulas to create a NamedRange in Google spreadsheet
so if I want to create a Dynamic Range say to read as many entries as there are in column A, the best I can do is to start with creating a NamedRange for column A:A which will use all the rows available in the related sheet
and then I can operate on that ... as shown in a WorkAround (Sort Of) in the following illustration