Forum Discussion
change a cell based on dates from a data sheet in the same workbook
I have a data sheet with all my employees listed and it has their expiration dates from drivers license, med card and MVR expiration. Is there a way I can highlight a cell on other worksheets in the same workbook to change color when one or more of the dates are about to expire or have expired in the data sheet?
Example: I have a cell on tab one with Johns Name. Can I change the cell color of that cell depending on the dates in tab 2 for John.
John is in cell w2 on tab one.
Johns expiration dates are in cells L2-N2on tab two.
7 Replies
Let's say the sheet with the employee info is named Employees, with the employee name in column A.
On the other sheet, select the range with employee names in column W. I will assume that W2 is the active cell within the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=OR(VLOOKUP(W2, Employees!$A:$N, 12, FALSE)<TODAY(), VLOOKUP(W2, Employees!$A:$N, 14, FALSE)<TODAY())
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK.
You can do the same with variations of the formula and other colors if you wish.
HansVogelaarIn your Formulas what does the 12 stand for?
I need to know 30 days or 1 month ahead as a warning and then when it is expired to show red. For one of my Drivers It shows red but nothing expires for 6 months.
It's slightly more complicated. See the attached version.
I used two defined names; you can view their definition in Formulas > Name Manager.
