You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

thanpisit19 Author

Level 1

22 points

Numbers: Keeping the same reference

Hi,

I am trying to create a spreadsheet where new data will be added regularly and I am wondering if there is a way to keep the cell reference the same.

For example, I created a table of 4x4 ColumnA-D Row1-4. My row1 will always be fixed on top. Say I want my A1 to always have a value of C2*D2. As that is where my latest data will be. Every now and then I will be adding a new row between row1 and row2. The original row2 would then become row3 and my A1's value would have shifted from C2*D2 to C3*D3, this is what I am trying to avoid. I want the value to remain as C2*D2 no matter how many new rows I will be adding in between row1 and row2. Any idea how to do this?

Thanks,

Best

MacBook Pro 13″, macOS 11.4

Posted on Mar 29, 2022 9:49 PM

Top-ranking reply

SGIII

Level 8

35,972 points

Posted on Mar 29, 2022 10:09 PM

One way is to use the OFFSET function.

The formula in A1:

=OFFSET(A1,1,2)*OFFSET(A1,1,3)

Or, if your region uses , as a decimal separator:

=OFFSET(A1;1;2)*OFFSET(A1;1;3)

The first parameter in OFFSET is the base cell, here A1. The second parameter says move one row down from the base cell. The third parameter says to move 2 (or 3) columns to the right of the base cell.

More on OFFSET, with many examples, can be found here.

View in context

Numbers: Keeping the same reference

Similar questions