change a cell based on dates from a data sheet in the same workbook

Copper Contributor

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

@richardadair31 

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.

@Hans VogelaarIn 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.

@richardadair31 

It's slightly more complicated. See the attached version.

 

S0001.png

S0002.png

 

I used two defined names; you can view their definition in Formulas > Name Manager.

@Hans VogelaarI 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.

@richardadair31 

1) Since your data on the December sheet begin in row 7, the defined names should be created while you're in row 7, and they should refer to December!$B7.

2) Since the name of the Driver Info sheet contains a space, you must enclose it in single straight quotes in formulas: 'Driver Info'.

3) Since the expiration dates are in columns J to L, and not in L to N as you stated in your first post, the column numbers in VLOOKUP must be {10,11,12} instead of {12,13,14}.

See the attached version.

Thank you for the help. The Driver Info was the part that was messing me up. I did not know it had to have the single straight quotes when there is a space in the name. The date colums i had changed before i realized the numbers called for the columns. I was thinking the formula would see the older date of hire and date of birth. I had that part fixed just after i posted my last reply.

Again thank you for the help.