Conditional formatting based on multiple worksheets

[krguyton]

I need some help with conditional formatting. I have a file in which there several different work sheets (different company's) which all have a column for contract expiration dates. I have conditional formatting setup to highlight any dates that are set to expire in the next 180 days from the current day. I created a summary sheet with the titles of the other sheets (company names). I would like to apply conditional formatting to the summary sheet that will highlight the name of the company that has expiration's coming up. Basically, if a company has any contracts set to expire in the next 180 days, the cell for that company name should be conditionally formatted.

Any help is greatly appreciated. Thank you.

 

[krguyton]

bump

 

[Eric W]

Assuming your date column is in column C on each company sheet:

Book1
	A
1	Company
2
3	IBM
4	Apple
5	Microsoft
6
7
Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=COUNTIF(INDIRECT("'"&A1&"'!C:C"),"<="&TODAY()+180)	text	NO

 

[krguyton]

Thanks for the reply. How does the summary sheet formula know which cell is associated to which sheet? (i.e. which sheet corresponds to IBM expiration's and so on.)

 

[Eric W]

The formula assumes the sheet name is the same as the company name, so the contents of A3 is IBM, so it looks for a sheet named IBM. If that's not always true, you'll need to add a lookup table somewhere to find the sheet name from the company name. Like:

Book1
	A	B	C	D
1	Company		Company	Sheet
2			IBM	IBM1
3	IBM		Apple	Apple 2
4	Apple		Google	ABC
5	Microsoft		Microsoft	MS1
6
Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=(COUNTIF(INDIRECT("'"&VLOOKUP(A1,$C$2:$D$6,2,0)&"'!C:C"),"<="&TODAY()+180))	text	NO

 

[krguyton]

Thank you so much!

 

[Eric W]

Happy to help! Thanks for the update.

 

[krguyton]

The formula assumes the sheet name is the same as the company name, so the contents of A3 is IBM, so it looks for a sheet named IBM. If that's not always true, you'll need to add a lookup table somewhere to find the sheet name from the company name. Like:

Book1
	A	B	C	D
1	Company		Company	Sheet
2			IBM	IBM1
3	IBM		Apple	Apple 2
4	Apple		Google	ABC
5	Microsoft		Microsoft	MS1
6
Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=(COUNTIF(INDIRECT("'"&VLOOKUP(A1,$C$2:$D$6,2,0)&"'!C:C"),"<="&TODAY()+180))	text	NO

Doesn't seem to be working. I changed the formula slightly because my company list for the summary page is in the B column starting at B2 and the expirations of the various sheets are on column F, starting at F2. I added this formula using "new rule" under conditional formatting with the company cells highlighted. Am I missing something?

 

[Eric W]

Very possibly! It doesn't take much to make a formula not work, just a misplaced $ sign can do it. In order to figure out what's off, I need to see the formula you're using, the range you're applying it to, a sample of your lookup up table (where it is and what's in it), and confirm where the dates are in each company sheet. If you can use the XL2BB tool (see the button in the response box), that would be ideal. That's what I've been using, and you can see how useful that is.

 

[krguyton]

Here is the formula: =(COUNTIF(INDIRECT("'"&VLOOKUP(B1,$F$2:$G$6,2,0)&"'!F+$F$2:F"),"<="&TODAY()+180))
Here is the Summary page also showing the conditional formatting:

Here is the date location on the company sheets:

Should the summary page be assigned as a lookup table?