How to reference the <CELL ABOVE>
Is there a way to reference the cell above?
example:
=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@row1) 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.
Comments


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.)
Thanks

if they are divided into parent child rows just call the parent
=PARENT([Column5]2)
Where [column5]2 is the cell that the formula is in

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.
Cheers!

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!
Cheers!

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
Categories
Check out the Formula Handbook template!