Referring to another cell according to its position in relation to the other one

Julie Barbeau

Hello, is there a formula that makes it possible to refer to the cell that is under another specific cell, even if the lines that are under the reference cell may move? In the example below, I want the date in the orange box to always be equal to the date in the cell below(in yellow). The problem I currently have is that if I move the line above the orange line (between the grey line and the orange line), my formula follows and the reference is to the same line rather than to the new line directly below the orange line.

Mary_A

@Julie Barbeau You can use an absolute reference. Those are explained here:

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

Julie Barbeau

Already try that, it's not working. See below, my absolute reference is to cell $Date$9. But, if that row is moved on the the top, the reference follow and change to $Date$7 and what I what is that reference still on the cell below my orange line (so the new Row 10)

Julie Barbeau

@Mary_A As the ID line still the same, is there a way to use this ID line to refer always the cell above even if the ID line is not necessary the following number if I move row (see below)? Thanks for your help :)

Mary_A

@Julie Barbeau Let me see if I follow you. So, the absolute reference is working meaning , when you move either the Date Source or the Date Reference cell, the reference resolved. -- you don't want to use that.

You want to have a positional reference to a source in relation to the cell with the formula, something like.

=[Date Source]$above

=[Date Source]$below

Such that if rows are added above or below a reference, the value is taken from the new value at that position. Similarly, if you move the referring row, it takes its value in relation to its new position. Is that correct? That would be cool but I couldn't find a way to support this. 😔 Maybe someone else can sort this out.