Wednesday, January 22, 2014

yogi_Using INDIRECT Function To Define A Range In Google New Sheets And Comparing Its Behavior With That In Excel

                                          Google Spreadsheet   Post  #1493
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-22-2014
question by Matthew Arrott (http://productforums.google.com/forum/?zx=umogbowmaaqk&usp=sheets_web#!category-topic/docs/spreadsheets/n9Y4Ns0cCzg)
Use of the "Indirect" function to define a range yields a parse error
Help!


In Excel and the last release of Sheets one can use the following expression to define a range:

  indirect(cell address):indirect(cell address)

By example:

Given:
  cell "A1" = 'Sheet Name'!$A$10
and
  cell "A2" = 'Sheet Name'!$A$19
then to sum over the range of 'Sheet Name'!$A$10: 'Sheet Name'!$A$19 the following expression represent an adjustable range summing mechanism:
  cell "A3" = sum(indirect(A1):indirect(A2))

In the new Sheets this throws a parse error. 

We are highly dependent on the formulation in our style of worksheets. Will this be corrected going forward or is the indirect(address):indirect(address) range formulation now considered malformed?

Many thanks,

Matt
--------------------------------------------------------------------------------------------------------------------------------------------