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

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

Reply
Question marked as Top-ranking reply

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.


SG

Similar questions

2 replies
Question marked as Top-ranking reply

Mar 29, 2022 10:09 PM in response to thanpisit19

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.


SG

Numbers: Keeping the same reference

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.