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

Lordkit1

New Member
Joined
Oct 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,984
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 23, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,984
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Forum statistics

Threads
1,175,769
Messages
5,899,391
Members
434,767
Latest member
ASB21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top