How do I flag a date in Excel on one sheet if there's something in a column on the same date in another sheet?
I have a workbook with a job sheet that has technician hours under a column for each day of the year. In the same workbook I have a VACATION sheet with all the days of the year. If there is an entry in a date column on the vacation sheet, I want a cell on the job sheet to have a V (or whatever) to show that "hey, somebody has a vacation day on that day!"
How would I do this?
Thanks!
How would I do this?
Thanks!
Frank McCourry
Use Vlookup This tutorial should help: http://office.microsoft.com/en-us/excel-help/introduction-to-vlookup-RZ101862716.aspx?CTT=1&client=1§ion=2
You can use formula like,
=IF(ISERROR(MATCH(B5,Vacat
Change the reference B5 to the Date cell in you Date column on the Technician Hour sheet,
Change $A to whichever column the date is stored on the Vacation Sheet.
If you don't like just a V as the result, then Change "V" to anything you like with " and " enclosing it.
=IF(ISERROR(MATCH(B5,Vacat
Change the reference B5 to the Date cell in you Date column on the Technician Hour sheet,
Change $A to whichever column the date is stored on the Vacation Sheet.
If you don't like just a V as the result, then Change "V" to anything you like with " and " enclosing it.
ASKER
It's not returning anything...I must be missing something. What should I look at?
Which column is the Date stored on the Technician Hour sheet?
Which Column is Date stored on the Vacation Sheet?
What are the exact sheet name of both sheets?
I understand the data is probably considered sensitive. Can you give me the Column structure of both sheets, and at list 1 rows of dummy data from each sheet?
Which Column is Date stored on the Vacation Sheet?
What are the exact sheet name of both sheets?
I understand the data is probably considered sensitive. Can you give me the Column structure of both sheets, and at list 1 rows of dummy data from each sheet?
ASKER
G on the tech hour sheet (starts at g17)
C on the vacation sheet (starts at 11)
Robert Morgan, Vacations (sheet names)
C on the vacation sheet (starts at 11)
Robert Morgan, Vacations (sheet names)
ASKER
No real data in there yet., so here be it. I was playing around with some formulas, so just ignore that (in row 16 on the Robert Morgan page)...
Ok!
So the formula should sit on the Tech Hour sheet somewhere where the column is empty. Possibly H17.
=IF(ISERROR(MATCH(G17,VACA
After that, copy the formulated cell down the column to cover all the records on the Tech Hour sheet.
So the formula should sit on the Tech Hour sheet somewhere where the column is empty. Possibly H17.
=IF(ISERROR(MATCH(G17,VACA
After that, copy the formulated cell down the column to cover all the records on the Tech Hour sheet.
The reason why the formula not returning anything is because of the =IF(ISERROR.
=IF(ISERROR(MATCH(G17,VACA
In the above formula, in english word, it means Match Date sitting in G17 in the Range C11:C5000 on Vancations sheet. If no date match (IsError), then don't show anything. If a match found, then show letter V.
=IF(ISERROR(MATCH(G17,VACA
In the above formula, in english word, it means Match Date sitting in G17 in the Range C11:C5000 on Vancations sheet. If no date match (IsError), then don't show anything. If a match found, then show letter V.
ASKER
H17 isn't empty...and copy it down the columns on the tech hours sheet?
Which column on the Robert Morgan sheet do you want this Flag to sit in? Put the formula in that column, Row 17.
Yes, you are right. Copy it down the column for all the rows that contain a date in column G you want to check.
Yes, you are right. Copy it down the column for all the rows that contain a date in column G you want to check.
ASKER
I'd like it to sit on the same row (G)...you'll see how I'm doing totals for each date in the file attachment.
Each column only contains one date, so it doesn't go down the rows...it goes across columns.
So if G17 says it's 8/28/2013, I want it to check C9 on the VACATION sheet (same date) and then check C11:C20 to see if there's an X. If there is an X, I want the G column (G16 for example) to show the V.
Each column only contains one date, so it doesn't go down the rows...it goes across columns.
So if G17 says it's 8/28/2013, I want it to check C9 on the VACATION sheet (same date) and then check C11:C20 to see if there's an X. If there is an X, I want the G column (G16 for example) to show the V.
There is no file attachment. Can you try attache your work book again? I can fill in the formula for you.
Make sure you have to press Upload when attaching file. Also, you must give it a short description.
Make sure you have to press Upload when attaching file. Also, you must give it a short description.
ASKER
You'll see the Vacation Noted line on the Robert Morgan sheet (top middle)...
MANPOWER.xlsm
MANPOWER.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's got it! Thanks HarryHY! More questions coming (working on a project due tomorrow). I'll link to them when I put them up. Thanks!