Drag formula with importrange across column in google sheets

0
votes

I'm trying to drag formula with Importrange across column in google spreadsheets.

I managed to drag the formula down using &row(A7) but I'm not able to find a way to drag the formula across. Is there a similar function like &column(A7)?

For example:

=IF(Isnumber(SEARCH("(milk)",IMPORTRANGE("urlBlaBlaBla","Sheet1!A"&row(A7)))),"buy now",IF(Isnumber(SEARCH("(tea)",IMPORTRANGE("urlBlaBlaBla","Sheet1!A"&row(A7)))),"later"))

Dragging down does look like:

=IF(Isnumber(SEARCH("(milk)",IMPORTRANGE("urlBlaBlaBla","Sheet1!A"&row(A8)))),"buy now",IF(Isnumber(SEARCH("(tea)",IMPORTRANGE("urlBlaBlaBla","Sheet1!A"&row(A8)))),"later"))

Now I would like to drag that across in a way that it refers to the next column, so A changes to B, something like:

=IF(Isnumber(SEARCH("(milk)",IMPORTRANGE("urlBlaBlaBla","Sheet1!B"&row(B7)))),"buy now",IF(Isnumber(SEARCH("(tea)",IMPORTRANGE("urlBlaBlaBla","Sheet1!B"&row(B7)))),"later"))

but that above does not happen automatically.

1
google-sheetscopydragimportrange
If your goal is just to drag from one column to another you simply need to follow the syntax of IMPORTRANGE, IMPORTRANGE(spreadsheet_url, range_string). It could be like this IMPORTRANGE("urlBlaBlaBla", "Sheet1!B:B") to wherever column you want it to drag. That's how I understood your question. – MαπμQμαπkγVπ.0
Thank you for your respond. Unfortunately that does not work on my sheet. If I drag "Sheet1!B:B" across to the next column, it remains as "Sheet1!B:B" or should I say the reference stays to the same cell. – Julia

1 Answers

0
votes

This is what you have now in trying to pull "dynamic" column and row references into a string address function

"Sheet1!A"&row(A7))

column(A1) does not work because it brings in the column as an integer. There are a couple of different solutions, in keeping the theme of bringing in the letter notation for the column you can use the left and cell functions:

left(CELL("address",A1),2)

(result for this function is "$A")

This is where we are using the CELL function to pull the string Address of A1 and then a LEFT function to get the column absolute reference for that column as a letter. Together it would be like:

="Sheet1!"&left(CELL("address",A1),2)&row(A1)