Match cell value (Date) in a named Range with function

G

gm

I'm hoping this one is pretty simple. Here's what I'm trying to
accomplish

I have a named range (A1:C5), we'll call it VacDates on worksheet
"VACATION". The format of the dates is mm/dd/yyyy.

I have a table on second worksheet (we'll call it "CAL") that is laid
out like a calender w/ dates starting at 1/1/2006 thru 12/31/2006. The
foramt of the table is two months wide and six months long, totaling
the 12 month calendar. Each day of the year is represeneted on this
sheet

Finally I have another worksheet (we'll call it GRAPHCAL) which is laid
out identical to "CAL" worksheet, but this one has no dates. Cell
locations are matched up for worksheet "CAL" and "GRAPHCAL" as well as
my calender worksheet. So 1/1/2006 is in the same cell coordinates on
both worksheets.

This is the sheet (GRAPHCAL) that I want to enter my formula in.

I want to get the date value from "CAL" from the matching cell
coordinate that my formula is in. Example: On "GRAPHCAL" I am at cell
location "A1", knowing that I want to get the date value from "CAL!A1"
[value=1/1/2006 ]. I then want to take this date and see if it matches
any date is in the named range, "VacDates" on worksheet "VACATION". If
there is a match then place the value of "V" in cell "GRAPHCAL!A1". If
not leave cell blank

I then have conditional formating set to change the cell color on
"GRAPHCAL!A1" based on the value of "V".

Any help would be appreciated. I have tried many variations of IF,
VLOOKUP, MATCH, LOOKUP, etc and can not get it just right & have been
banging my head against the wall. VLOOKUP does not seem to be the right
answer for this because it can only return a value in a specific cell
base on the match location. Here is one function I have that almost
does what I want but not quite:
1/1/2006 1/1/2006,nmd rng
1/2/2006
=IF(CAL!A1=IF(ISERROR(VLOOKUP(CAL!A1,VacDates,1,FALSE)=TRUE),"x",CAL!A1),"V","")
 
M

Max

In sheet: GraphCal,
Try in A1: =IF(COUNTIF(VacDates,Cal!A1)>0,"V","")
Copy across/down to suit
 
G

gm

Max, That did the trick! Thanks for the help. Much simplier then the
approach I was taking
Thanks again, my headache is gone (atleast this one!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top