Conditional Fomatting with INDEX and MATCH [SOLVED]

[kinkart]

Hello,


I finally registered after using the info on your site for a while.


I've been asked to create a vacation calendar for an international team. I downloaded the template you made, but it was too complicated for me to edit, and too complex for our needs.


My company's VPN does not allow me to access the websites listed to host the file, sorry for using pictures instead!

UPDATE:

Finally found a site that is not blocked. the sample workbook is here:

http://www.fileconvoy.com/dfl.php?id=gc5551333087331e59993109177fb059a1da25a349


Now I'm stuck with some conditional formatting.

MY SET UP:

- sheet 1 "Holiday Planner":

http://i40.tinypic.com/2wcprly.png


- sheet 2 "CALCULATIONS"!

http://i42.tinypic.com/eupwk1.png


WHAT I NEED:

using conditional formatting, I want to highlight all the cells on sheet 1 E13:N23 that are bank holidays, as listed per country in sheet 2.

As you can see, in sheet 1 column A it shows the country of the rep, so only the bank holidays for that particular employee should be highlighted.


Reading here and via some pages found on Google, I guess I should be using INDEX and MATCH in the conditional formatting, but after multiple attempts, my brain just fried so I'm hoping someone here will be able to help.


Many thanks, and keep up the good work!

 

[NARAYANK991]

Hi ,


It would have been nice if you could have uploaded your workbook ; now we will have to exchange several posts to find out what is what.


Can you clarify what is present in rows 10 , 11 and 12 ? Are there dates anywhere in these cells , or are they just numbers and text ?


Narayan

 

[kinkart]

yes I know, sorry again about that, the VPN doesn't even allow dropbbox :-(


In row 10, it's the date, formatted to show only the full month name.

In row 11 and 12, column E is =E10. Row 11 is formatted to show the day (ddd), row 12 to show the day number (dd).

the rest is just the previous cell +1 to increment.

 

[kinkart]

update: finally found a website not blocked by my VPN. see link in my first post. Thanks again!

 

[NARAYANK991]

Hi ,


Thanks.


Select the entire range E13:NE23 , and enter the following formula in CF :


=ISNUMBER(MATCH(E$12,INDEX(Dates,0,MATCH($A13,Countries,0)),0))


where Countries and Dates are named ranges , defined as follows :


Countries refers to : =CALCULATIONS!$A$12:INDEX(CALCULATIONS!$12:$12,COUNTA(CALCULATIONS!$12:$12))


Dates refers to : =OFFSET(CALCULATIONS!$A$13,,,30,COLUMNS(Countries))


Narayan

 

[kinkart]

awesome!!! Many thanks Narayan, works perfect!