Hi
Attached is an example xl sheet where i am struggling a bit with the conditional formatting of cells. The sheet is simplified for this question. The following is the scenario
Sheet 1: is a summary sheet which gives the number of occurrences of data month-wise. Data is in the second sheet.
Sheet 2: contains dates in one column and Pass/Fail in the second column. Dates in the first column are refreshed as they are obtained from the database on a sql query. The Pass/Fail column is a formula column which gets calculated based on data in other columns.
I could successfully get the counts for occurrences of data in a particular month on the Summary sheet. - e.g. 2 for Jan, 3 for Feb and so on.
What i am struggling with is to find a conditional format formula under the following scenarios:
1. Check the whole dataset in column 1 to find the month and if its Jan e.g. row 2 and 3 then check the corresponding values in the 2nd column.
- If all are PASS for that month then format the month cell (B2) in Summary sheet to green
- If all are FAIL for that month then format the month cell (B2) in Summary sheet to red
- If some are PASS and some FAIL for that month then format the month cell (B2) in Summary sheet to orange
What should the conditional format formulae be to achieve the above. Both the dates and the Pass/Fail columns have named ranges - mthRange, PFRange.
Any help is appreciated.
docGee
Bookmarks