Thursday, April 28, 2011

yogi_ImportRange Function To Import Cells Based On A Specified Row Pattern

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
ovz79 said:
How do I add a set value of 6 to the cell value in this formula for 50 formulas on a spreadsheet: =importrange("abcdefg", "sheet1!d35")
The cells I am importing are spaced evenly at 6 intervals and I want to save time so I don't have to re-write this formula 50 times but can just drag it down the spreadsheet. In other words, cell 1 shows =importrange("abcdefg", "sheet1!d35") and cell 2 is =importrange("abcdefg", "sheet1!d41"), etc. without having to type in d41, d47, d53.
ImportRange function is an expensive function in that there is a limit on the number of ImportRange functions that can be used in a spreadsheet. So in the following solution I have made only one ImportRange function call and then operated on that to import the rows based on the specified pattern of rows.

The following image shows the spreadsheet on which we are going to make the ImportRange function call