RE: Conditional Formatting Query

[Bazola]

RE: Conditional Formatting Query

Hi All

I have a spreadsheet where we are capturing Go live dates for a project, the sheet is called "OTR Sites Dashboard", the "Site Proposed Go Live" column is E, we then have another sheet called "Bank Holidays" and that looks up the sites from the OTR Sites Dashboard and then have a column for each bank public bank holiday within EMEA.

I need it to highlight the OTR Sites Dashboard Column E if there is a duplicate date so it shows us its a bank holiday in that country so we can change the date of migration. I have got it working using match formula: -

• <code style="font-family: "Courier new"; white-space: pre-wrap; color: rgb(69, 69, 69); font-weight: 600;">=MATCH($E$3,'Bank Holidays'!$E$3:$Y$3,0)</code>
• However I am not sure how to make this apply to all the other columns as well E3:E60 and 'Bank Holidays'!$E$3:$Y$60) for example. Your help is appreciated.

 

[Special-K99]

Re: Conditional Formatting Query

Try this (untested)

Select the entire range in column E, Im assuming data starts on E3

Conditional Formatting
New Rule
Use a formula to determine...

=(COUNTIF('Bank Holidays'!$E$3:$Y$60,E3)>0)

Format as required

NOTE: Some versions of Excel don't allow Condtional Formatting where the range is on another sheet. The workaround is to define the range on the other sheet as a Named Range and use that Named Range instead of a reference.

 

[Bazola]

Re: Conditional Formatting Query

HI

Thanks very much for your help, that worked!

 

[Bazola]

Re: Conditional Formatting Query

Hi
After doing some more testing, this doesnt work as required unfortunately. I will try to explain the requirement

I have a sheet called "OTR Sites Dashboard" and there is a column called "Site Proposed Go Live" (Column E) Column A, B C and D contain details about the sites, country, site name, headcount etc. I Have another sheet called Bank Holidays, this references the "OTR Sites Dashboard" sheet and pulls in the country, site name, and Site proposed go live, It then has additional cells for each site from D: X for example D3:X3 for London Office - these will be configured for the bank holidays for that country / site.

I need conditional formatting configured within the OTR Sites Dashboard sheet within the Site Proposed Go live Date column E to check if its a bank holiday against the other Bank Holidays sheet for that site, and if so fill the box in red. However I also need it to consider the UK Bank holidays as well at the same time as that is where the team is based doing the work at these sites.

So essentially a check to look at the site go live date, then check it against the UK bank holidays + sites bank holidays for that country / city and then mark the cell red if the dates match. As the UK has the same bank holidays, I have put them all into a single Row at the top within the Bank Holidays sheet ='Bank Holidays'!D3:X3, Ive then populated all the UK sites with this formula so they fill in automatically on the Bank Holiday sheet =IF(ISBLANK($L$3), "", $L$3)

I hope you can help as this is driving me mad!

Thanks

 

[Bazola]

Re: Conditional Formatting Query

Hi All

Anyone got any suggestions on this? I have been working on it all day and still struggling

 

[Bazola]

Re: Conditional Formatting Query

Hi All

I have managed to get it working by adding in an additional column on the sites otr dashboard sheet (Bank holiday go live clash) and modifying the other sheet bank holidays so each site includes both the uk bank holidays and the country / city, it then checks for duplicates against the go live date.

This is the formula I have used in the bank holiday go live clash cell =IF(COUNTIF('Bank Holidays'!D4:AJ4,'OTR Sites Dashboard'!E3)>0, "Clash", "No Clash")

However, I now found that if I sort the sheet, the formula then gets messed up and provides incorrect information.

Hopefully there is something simple I am missing here?