Index sheet indication of status of following sheet information.

**John Brooks** · 02-02-2012, 11:01 AM

I am constructing a workbook comprising of several pages. The first sheet which I call Index contains a list of sheet names which are hyperlinked to sheets which follow the index sheet. One column called Live issue in each of the following sheets contains a Yes or No as part of the details entered in each of the rows. What I want to do is to be able to display on the Index sheet in the cell to the right of the hyperlink a colour red to indicate if any of the cells in the Live issue column contain Yes or green if none of the cells contain Yes. I am using excel 2007.

Any help would be greatly appreciated.

Thanks
John

**Simon Lloyd** · 02-02-2012, 11:51 AM

Couldn't you just use conditional formatting? in the adjacent cell click conditional formatting and another rule enter =COUNTIF(Sheet2!$A$1:$A$100,"Yes")>0 but you can change for a dynamic named range as it's easier to maintain

Google Me

Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

**John Brooks** · 02-03-2012, 08:03 AM

Thanks for that Simon, I managed to use COUNTIF for the Yes occurrences then I conditionally formatted the cell in Index to display red or green as appropriate. Works nicely.