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

Find more posts tagged with

Manufacturing

Formulas

IT & Operations

Project Management

Enhancement Request

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.