Help needed: Is there a way to make a number act like a relative cell reference?

[Lordkit1]

Okay so I'm probably as confused as you are hearing that question so lets explain.

I have a spreadsheet, and on it I need an alert system that checks the date Today() against ("the date in another cell" - one of 3 numbers of days [30,60,90]) and changes the value of the cell containing the formula into one of 3 alert levels accordingly;

EG: IF( TODAY() > (A1(12/12/1994) - 90) Then
G5 = Grey Alert.

Not particularly dificult but heres the catch.

Due to the maluble nature of the work i'm employed in I may have to move these columns about alot. To that end I have named every column as a named range;

EG:
nm = Named Range

IF ( TODAY()< (INDEX(nm, 1) - 90) Then
G5 = Grey Alert

The problem? My chart has over 300 entries and all of them will be archived and replaced over the span of a year. Either I figure out how to wipe the data of only a couple cells here and there, thus preserving my formulas, or as my question entails i find a way to make it so Excel automatically ensures the BOLD number in the above formula becomes reletive to the cell so that when a row is copied, pasted elsewhere and deleated that the formulas are updated.

Ideally I'd like to do this without VBA but if it's not possible I have been introduced to VBA and have a basic understanding.

Many thanks to all you people out there who might help with this!

 

[jasonb75]

I think that the description is more confusing that the requirement.

ROW()-MIN(ROW(nm))+1

As you're not using office 365, it is likely that you will need to array confirm the formula with Ctrl Shift Enter in order for it to work correctly.

 

[Lordkit1]

I think that the description is more confusing that the requirement.

ROW()-MIN(ROW(nm))+1

As you're not using office 365, it is likely that you will need to array confirm the formula with Ctrl Shift Enter in order for it to work correctly.

I'm unsure as to what your applying here? The cell containing the formula needs to display a word. Let me give you the full formula as it will have to be now:

IF(A1 = "", "", IF( TODAY() > (INDEX(rcCol_RevStartDate, 1) - 90), IF( TODAY() > (INDEX(rcCol_RevStartDate, 1) - 60), IF( TODAY() > (INDEX(rcCol_RevStartDate, 1) - 30), "Red Alert", "Amber Alert") , "Grey Alert") , "No Problem")

Hopefully, if i haven't screwed something up in the transcribing of it, the above code would check :

1. if cell A1 is empty, if so then display nothing otherwise:
2. Check if TODAY is greater then the 1st value of the named column "rcCol_RevStartDate" - 90, -60 and -30 respectivly. Then it would display either "No Problem", "Grey Alert", "Amber Alert" or "Red Alert"

the problem I'm facing is if a row is deleted in a column of 300 of these formulas, the index reference for rcCol_RevStartDate in bold would get out of sync with the row of the cell...


as I wrote this i figured it out i think? So instead of 1 you have ROW(Insert Self Reference here). Would that work do you think?

 

[jasonb75]

as I wrote this i figured it out i think? So instead of 1 you have ROW(Insert Self Reference here). Would that work do you think?

You can do it either way, but that was what I meant. ROW() with nothing between the parentheses is a self reference.

 

[Lordkit1]

You can do it either way, but that was what I meant. ROW() with nothing between the parentheses is a self reference.

Really? Thanks! I didn't know that!