How to reference the <CELL ABOVE>

edited 12/09/19 in Formulas and Functions

Is there a way to reference the cell above?


=IF(logical_expression, value_if_true, <CELL ABOVE>)

The simple way is to reference using [COLUMN_NAME]2 (the number being the current row number minus one). But this is broken anytime a row is deleted, which happens frequently on our sheet.

I have tried adding an "index" and "RowNum" column with the formulas; "=1+0", and "=SUM(index$1:index@row)" respectively, then I tried referencing the <CELL ABOVE> with INDEX([COLUMN_NAME]:[COLUMN_NAME], RowNum@row-1) but this returned the "#CIRCULAR REFERENCE" error.

There is a number of ways this can be accomplished in Excel including the OFFSET() and ROW() functions.


  • L_123
    L_123 ✭✭✭✭✭✭

    Something like this?


    =INDEX(a:a, COUNTIF(a1:a3, ISBLANK(@cell)) + COUNT(a1:a3))


    (In Cell A4)

  • Thanks for your response Luke,

    I couldnt seem to get your formula to work.

    The application of the formulas is to copy information from parent rows to their children rows. (Eg. Project status is included in the parent row, but this information needs to be included in each of the children rows for filtering and reports.)


  • L_123
    L_123 ✭✭✭✭✭✭

    if they are divided into parent child rows just call the parent




    Where [column5]2 is the cell that the formula is in

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    It's not elegant and is kinda cheating, but this works. Assuming we're working on Row 10:

    =IF([Column 1]10 = "Hi", INDEX([Column 2]$1:[Column 2]9, COUNTIF([Column 2]$1:[Column 2]9, NOT(ISBLANK(@cell))) + COUNTIF([Column 2]$1:[Column 2]9, ISBLANK(@cell))), "")

    It should eliminate the issue of deleted rows causing a #REF.

  • Thanks again Luke,

    Not sure how I overlooked using that!

    I used a combination of PARENT and INDEX(ANCESTORS(),[Parent Level]) formulas to reference parent and grandparent rows.


  • Thanks for the response Chris,

    I managed to avoid referencing <CELL ABOVE> altogether, but i'll keep your formula in mind, i'm sure it will come in handy sometime!


  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Glad to hear you worked it out! I'd love to have some of the extended functions offered by Excel. OFFSET, ADDRESS would make things a lot easier.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!