Have Excel cell reference a specific cell location

Question

Hi,

Please refer to the screenshots below for a better understanding!

I want the current Inv. column (U) in my sheet to always refer to the third column to the right (Column X). It works well but if I move any of the cells, then it doesn't work. For example, notice the blank row starting from column X. I want to move it up so there is no gap and cell U20 should say 6772 after I move the rest of the data up. Rather than saying 6772, it says #REF! and cell U21 continues to refer to 6772 but it should refer to the new value of 3 columns to the right- 6818.

Does this make sense? How can I reference the cell location even when moving around data rather than the value of the original cell location? Thank you!!

Answer 1

Hi, in U8 cell use the below formula and copy down:

=INDIRECT(ADDRESS(ROW(),COLUMN()+3))

Answer 2

You can use INDEX also as an alternative. Adapting Faysal Farooqui's formula

=INDEX(A:Z,ROW(),COLUMN()+2)

Answer 3

Thank you!!