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!
Kevin SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Frank McCourryV.P. Holland Computers, Inc.Commented:
0
Harry LeeCommented:
You can use formula like,

=IF(ISERROR(MATCH(B5,Vacation!$A$5:$A$500,0)),"","V")

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.
0
Kevin SmithAuthor Commented:
It's not returning anything...I must be missing something.  What should I look at?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Harry LeeCommented:
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?
0
Kevin SmithAuthor Commented:
G on the tech hour sheet (starts at g17)
C on the vacation sheet (starts at 11)
Robert Morgan, Vacations (sheet names)
0
Kevin SmithAuthor Commented:
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)...
0
Harry LeeCommented:
Ok!

So the formula should sit on the Tech Hour sheet somewhere where the column is empty. Possibly H17.

=IF(ISERROR(MATCH(G17,VACATIONS!$C$11:$C$5000,0))),"","V")

After that, copy the formulated cell down the column to cover all the records on the Tech Hour sheet.
0
Harry LeeCommented:
The reason why the formula not returning anything is because of the =IF(ISERROR.

=IF(ISERROR(MATCH(G17,VACATIONS!$C$11:$C$5000,0))),"","V")

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.
0
Kevin SmithAuthor Commented:
H17 isn't empty...and copy it down the columns on the tech hours sheet?
0
Harry LeeCommented:
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.
0
Kevin SmithAuthor Commented:
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.
0
Harry LeeCommented:
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.
0
Kevin SmithAuthor Commented:
You'll see the Vacation Noted line on the Robert Morgan sheet (top middle)...
MANPOWER.xlsm
0
Harry LeeCommented:
Now, I kind of know what you want. the above formula is not going to work.

You actually may not need something so complicated.

When you look at the Vacation Sheet, You have conditional count at the top. You can simply use formula like
=if(Vacation!C5>0,"V","")
in your Robert Morgan Sheet.

I can see that there are two count. One for Tech, and one for Prev Maint and of course the total count.
If the Robert Morgan sheet is referring to only Tech, you can use C3 instead of C5 in the formula.
If the Robert Morgan sheet is referring to only Prev Maint, you can use C4 instead of C5 in the formula.
If the Robert Morgan sheet is referring to any vacation, you can keep it as C5.

Once entered into G16 on Robert Morgan Sheet, just copy the cell all the way to the right, to JV16.
Copy-of-MANPOWER.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin SmithAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.