How to reference the <CELL ABOVE>

BP97

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@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

Something like this?

 

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

 

(In Cell A4)

BP97

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

L_123

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

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.

BP97

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!

BP97

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!

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.