Google Sheets -- Dynamically change cell reference

Question

Need a little help:

I have a link in J5 that is =DataIn!O2

I need cell O5 to link to DataIn!O243, however, I need to do it dymanically as other columns will need to use this as well. I need to be able to copy J5 into O5, T5, etc having the DataIn!O2 increase by 241 each time.

For example when I copy J5 which is =DataIn!O2 ,

O5 would be =DataIn!O246
T5 would be =DataIn!O487

Then copy into next row the values would be :

J6 =DataIn!O3
O6 =DataIn!O247
T6 =DataIn!O488

I tried =INDIRECT("DataIn!O"&2+241) but that did not work.

Any ideas?

Welcome. Would you please share a copy of your spreadsheet, and include an example of a successful outcome. — Tedinoz

ZektorH ZektorH · Accepted Answer · 2020-03-11T09:54:57

You can use the COLUMN() function.

This gets the column of the current cell.

Your function then can be INDIRECT("A"&(<X>), TRUE), where <X> needs to be a custom formula based on your description.

As an example:

Placing this formula on C1 and dragging it to the side (D1, E1, F1, etc)
Starting values on A1
Getting every 4 values (A1, A5, A9, A13, etc)

My <X> is <starting number>+(COLUMN()-<starting column number>)*<value interval>.

Translating to: 1+(COLUMN()-3)*4.

Google Sheets -- Dynamically change cell reference

1 Answers