Stop Excel from changing relative reference

[bruceha_2000]

Good day all,
This has been driving me nuts for months, perhaps someone can help.

I am using Row/Column referencing and my formula works fine UNTIL I cut a row and insert it at another location. Excel finds it necessary to change the relative reference such that it maintains a link to the original cell. What I WANT is for it to reference Column 3 in the row above THIS row no matter where I move it.

The spreadsheet auto calculates the next event number and time it starts.
The data might have a blank row so the formula must check for that before incrementing the Event number. Event is in Column 3, Time in Column 4:

Event Time
29 11:57 AM
30 12:09 PM
31 12:21 PM

The calculation for ALL of the event number cells should ALWAYS be:
=IF(R[-1]C3="",R[-2]C3+1, R[-1]C3+1)

But if I need to move an event (they are grouped by similar event type), Excel changes the formula. For example, I 'cut' the row with Event 31 and insert it above Event 30.

I end up with this: =IF(R[1]C3="",R[-1]C3+1, R[1]C3+1)
and the data looks is now this:
29 11:57 AM
31 12:21 PM
30 12:09 PM

I've been fixing it by copying the formula from the first row across all subsequent (non blank) rows. However that is a PITA, is there some way to force Excel to maintain THIS row -1??

Thanks,
Bruce

[shg]

You can use named relative ranges.

Select cell C3, do Insert > Name > Define,

up1 Refers to: =C2
up2 Refers to: =C1

Then change your formula to =IF(up1 = "", up2 + 1, up1 + 1)

[mikerickson]

You could use Named Ranges as a work around.

Name: R.1C3
Refers to: =OFFSET(!R1C1,ROW(!RC)-2,2,1,1)

can replace the R[-1]C3, while

Name: R.2C3
RefersTo: =OFFSET(!R1C1,ROW(!RC)-3,2,1,1)

replaces R[-2]C3.

Select a cell below Row 3 immediatly before entering these names.

[bruceha_2000]

I thank both of you PROFUSELY!!!

I would never have found that solution. I guess MS couldn't make it easy. However, it solves the specific problem I posted and with some effort figuring out how it worked, I used it to update 2 additional fields that are also auto filled based on other data in the sheet. No more 'copy the formula' dance when I rearrange the rows.

I'm sorry I didn't reply sooner. I posted after the last time I needed to make a schedule and didn't check back until I needed to make another one (which is now).

Bruce