2

I am trying to use an offset formula with importrange something as below. 

=OFFSET(IMPORTRANGE("googlespreadsheetkey","sheet!A1"),MATCH(A5,IMPORTRANGE("googlespre‌​adsheetkey","sheet!A‌​:A"),0)-1,1,2,2)

It is giving me

"Argument must be a range" error.

Seperately, offset and importrange both working fine.

Thank you for any work around.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
viv227295
  • 387
  • 2
  • 6
  • 17

3 Answers3

4

I think the error message means what is says - the first argument to offset must be a range (or what I would call a reference). So if you try doing this for instance

=offset(1,0,0)

instead of

=offset(A1,0,0)

you get the same error message.

In this case importrange is just giving you a set of numbers, not a reference to cell A1 in the other sheet.

If you imported the range into B1 (say) of the current sheet, then you could offset relative to that, but I don't know a way of using offset in another workbook.

So you have to do the import and the offset separately

=importrange("key","sheet1!A:C")

and then (assuming this imports into B1)

=offset(B1,match(A5,importrange("key","sheet1!A:A"),0)-1,1,2,2)

At least importrange is dynamic so if you update a cell in the original spreadsheet it will update in the current spreadsheet.

If what you are hoping to do is to select certain rows and columns from the imported data without having to import all the data into the current sheet first, there is a method for selecting certain rows from a range described here, so with the importrange it would look like this

=query({importrange("key","sheet1!A:C"), arrayformula(row(A:C))}, "select Col2, Col3 where Col4 <3")
  • this would give you the first two rows and the last two columns (my test data has three columns).
Community
  • 1
  • 1
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Hi Tom! Thanx for ur reply. I think you rightly found out why is the error. Actually, for some reason i cannot use them separately. Maybe there is someway to importrange cell reference rather than cell centent. Well, I need to see if there is any workaround for it even if it means to check if something else along with importrange can achieve this feat. – viv227295 Mar 18 '17 at 12:49
  • Thanks for feedback. I did notice when I copied your formula that there seemed to be some odd characters in it and I had to retype sheet!A1 and sheet!A:A – Tom Sharpe Mar 18 '17 at 13:16
1

I was also lookig to use Offset(importrange ...) but the simplest workaround I found it using an Index function with the following structure.

Index( importrange(xx), 0 , MATCH( B2, importrange(xx A1:ZZZ1)) )
in order to bring a full column OR

Index( importrange(xx), MATCH( B2, importrange(xx A:A)) , 0 ) in order to bring a full row

Where the "0" is the key so the function brings all the rows of the desired string o column.

System 22 Services
  • 11
  • 1
0

Thanks a lot @Tom Sharpe for the "query thing".

I simplified it a little :

=query(importrange("url of the external google sheet","sheet1!$B:$E"), "select Col2, Col3 where Col4 < 3")

(I found that $ is generally a good practice for importranges if you want to let your Google sheets a little bit more independant from each others)

Several tricks to succeed in using that :

  • select is a sql-like but special google syntax, without from clause in particular
  • in this syntax, column names are imposed by Google and are 'Col'+column number, 1-based (so, first column of the range is Col1, the second is Col2, ..., so here Col4 is $E in the source sheet)
  • and those column names are case sensitive ! (so, don't try it with col2 as I did first ...)
herve-guerin
  • 2,827
  • 20
  • 25