Vlookup date, but leave cell empty if no date is found?

Kentetsu

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
Once again I must humbly come to the great minds for assistance. :)

I have a spreadsheet that I am setting up to track training dates. Using Vlookup, it will return the date of training in each of many categories for each employee. Some employees will not require some of the training, so the date cell will be blank. But on my tracking sheet, if the Vlookup finds no date it fills the cell with 1/0/00, and I need to be rid of that.

I considered using conditional formatting, but I am already using that to identify training that is about to expire. If the date returned is within a month of expiring (annual training) then it will change the font to purple, and if it is within two weeks of expiring then the font will be red. So, when I tried to make any 1/0/00 entry change to a white font to make it invisible, it just doesn't work. This is the Vlookup I am using:

=VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)

Any assistance with this would be greatly appreciated. Thank you!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Kentetsu said:
Once again I must humbly come to the great minds for assistance. :)

I have a spreadsheet that I am setting up to track training dates. Using Vlookup, it will return the date of training in each of many categories for each employee. Some employees will not require some of the training, so the date cell will be blank. But on my tracking sheet, if the Vlookup finds no date it fills the cell with 1/0/00, and I need to be rid of that.

I considered using conditional formatting, but I am already using that to identify training that is about to expire. If the date returned is within a month of expiring (annual training) then it will change the font to purple, and if it is within two weeks of expiring then the font will be red. So, when I tried to make any 1/0/00 entry change to a white font to make it invisible, it just doesn't work. This is the Vlookup I am using:

=VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)

Any assistance with this would be greatly appreciated. Thank you!
Click to expand...

Try...
Code: 
=IF(VLOOKUP(J4,X!$A$4:$AN$16,4,0)="",
     "",
     VLOOKUP(J4,X!$A$4:$AN$16,4,0))

where X is:

'[Required training by position PII presentation.xls]Employee Dates'
 
Last edited:
Upvote 0
=IF(ISBLANK(VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)),"", =VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE))

thanks

Kaps
 
Upvote 0
Okay, this isn't working for me, but I think I'm screwing it up trying to copy/past. I did try typing it all in by hand but that hasn't worked either because I keep getting an error message. I'll keep trying, but if I am doing something wrong please clue me in. Thanks...
 
Upvote 0
Kentetsu said:
Okay, this isn't working for me, but I think I'm screwing it up trying to copy/past. I did try typing it all in by hand but that hasn't worked either because I keep getting an error message. I'll keep trying, but if I am doing something wrong please clue me in. Thanks...
Click to expand...

What is the error message you get?
 
Upvote 0
The error had to do with symbols used, but I think I had spaces or something where I did not need them.

One other quick question if you have the time; is there a way to copy the forumula to adjacent cells without having the manually change the formula each time? If I copy to the cell next to it, then the reference cell also changes one position. I can almost remember what I am doing wrong, but can't quite grasp it. Thank you...
 
Upvote 0
Kentetsu said:
The error had to do with symbols used, but I think I had spaces or something where I did not need them.

One other quick question if you have the time; is there a way to copy the forumula to adjacent cells without having the manually change the formula each time? If I copy to the cell next to it, then the reference cell also changes one position. I can almost remember what I am doing wrong, but can't quite grasp it. Thank you...
Click to expand...

Are you wanting to fix J4 like this: $J4 or is it something else?
 
Upvote 0
I think you just gave me my answer. The Vlookup reference should always be in column J, but when I copy the formula to the next column it will change it to K and then I have to manually change it back.

I'll give it a shot and let you know. Thank you once again...


Nope, now I get an N/A message if I insert the $ sign...

When I copy the formula to the next column, I want the Vlookup to still reference the information in column J, rather than changing it to the next column over. If possible, I would also like to change the column on the other sheet where it gathers it data to enter into the cell. So that, in the first cell it looks (on the other sheet) at column 4, I would like it to look at column 5 instead. Is this possible, or more trouble than it is worth?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
304
MountainFog
M
L
  • Question
Another VLOOKUP Question
Replies
4
Views
267
LogPlanner
L
J
  • Question
Lookup starting at bottom of page with criteria
Replies
3
Views
184
Alex Blakenburg
A
S
  • Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
79
awoohaw
awoohaw
W
  • Question
Excel Summary Report Display Expired Dates From Multiple Sheets
Replies
9
Views
807
awoohaw
awoohaw

Forum statistics

Threads
1,214,981
Messages
6,122,565
Members
449,089
Latest member
Motoracer88

Share this page

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
Back
Top