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","")
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","")