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