Forum Discussion
change a cell based on dates from a data sheet in the same workbook
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.
HansVogelaarI have tried the formulas over and over again.It doesnt seem to be working on My main Workbook. Ive attached what im working with and included the data minus the confidential stuff.
I will say I have only really been messing with Excel for about a year and it really has never been this in depth. This workbook we use to keep track of almost everything in our Trucking business. Every month I add a new tab and copy all this blank info into it. I would love to be able to use this same formula so and put it where it needs to go and that way no matter what month tab anyone is in they can see if any of our drivers has an issue.
Thank you for all the help btw. I kinda got it working before with just the first one. But then is was messed up on a guy and nothing was expired.
- I should Mention I am using Office 365 on a Mac. This is also a shared workbook.