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   Jan-22-2014
question by Matthew Arrott (!category-topic/docs/spreadsheets/n9Y4Ns0cCzg)
Use of the "Indirect" function to define a range yields a parse error

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:

  cell "A1" = 'Sheet Name'!$A$10
  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,