Excel: marking date records on a calendar 1

[jonsi]

I have a series of records of appointments in Sheet 1 which is derived from another source (.CSV file)
The records have formula driven output to indicate appointment type 1,2 or 3 against each of the dates in the record ... so far so good - Dates in col C, Output in col F
Not every date has a record accompanying it so not all dates are shown, only those with appointments.
On sheet 2 of the same workbook, I need to display these outputs on a graphical representation of a calendar (using Output and Conditional Formatting??) month by month
I can make the month selectable to display all the dates in that month, so now I want to apply the record data to that calendar
what I'm after is something showing all the dates in a selected month (check) but populated with entries from the records in sheet 1 (a bit like a year planner but per month) and this is the bit I'm struggling with.

In a nutshell, I need the calendar to only show a marker on dates where there has been an appointment of type 1,2 or 3 - leaving all others empty.

Is this do-able? I've tried Vlookup without success as it finds closest match rather than definite match.

thanks in advance




hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[randy700]

I've tried Vlookup without success as it finds closest match rather than definite match.

Setting the fourth parameter of the VLOOKUP command to FALSE forces it to find an exact match.


Randy

 

[SkipVought]

VLOOKUP() requires a fixed table structure where the lookup range is ALWAYS the first column in the VLOOKUP() range.

This may be the case in many instances, but I have found that there are other cases where the lookup range happens to be otherwise located.

Therefore, I almost always use INDEX() & MATCH(), which, along with leveraging Named Ranges and Structured Tables (two very powerful features of Excel), make for a powerful and versatile tool.

As Randy pointed out, VLOOKUP() will return a value for an exact match with the optional range_lookup argument of FALSE. In the MATCH() function the optional match_type argument would be 0.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jonsi]

As ever Skip your advice was A1. INDEX & MATCH works a treat. Never used it before but I can see it saving me a lot of time in the future. Have a Star!

thanks
Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"