Conditional formatting
Posted by Peter on November 07, 2000 2:02 AM
Bit of a long explanation, sorry.
EXCEL 7 on NT4
I have a workbook, where SUMMARY sheet is the summary status for the next 7 sheets. All sheets are same format and layout
The 7 sheets show the status of various projects, shown only as Red, Amber, or Green, where the cell colour is formatted to the letter R, A , G value in the cell.
Each sheet has the same 30 projects shown against five locations. So as each location (column) status changes across each project(row) the colour will go from Red (running and slipping) through Amber (running and could slip) to Green (either running to time or complete). The workbook is linked to shared worksheets across the WAN as the locations are in various parts of the country, and they update their worksheets. That bit works
I need the summary to show if one location is reporting their part of the project as RED, then the summary will show RED. If all green, summary is green, and if mixture of green/amber, the summary to show amber, so there is or could be, an element of min/max or >= in the formula, but I have tried so many! Conditional formatting and COUNTIF seem the best, except that COUNTIF will not view a workbook range. Apart from nesting seven IF statements, with their conditions, anyone any ideas. A couple of Formulas tried are:-
This type of nesting sort of works, but is a pain
=IF((COUNTIF('sheet2'!F3:H3,"g")>=3)*(COUNTIF('sheet 3'!F3:H3,"g")>=3)*(COUNTIF('sheet 4'!F3:H3,"g")>=3)*(COUNTIF('sheet 5'!F3:H3,"g")>=3),"g","a") etc
or if I try
=IF(COUNTIF('sheet 1:sheet 7'!F5,"g")>=1,"g","a")
I get the meaningful #VALUE! result
Doesn't seem to matter if I enter them as array or standard
Simple isn't it????
thanks
Peter
Posted by Ivan Moala on November 08, 2000 2:18 AM
Peter
There are couple of ways to do this....some of them
long winded....but first of the problem lies in the
fact that CountIf is one of those functions that is
NOT 3d enabled ie. it does not work across sheets
There is a work around code for this developed by
By Myrna Larson and David Hager......how ever
using it may can be a bit of work for diff criterias.......here is the code for it.
Give credit to Myrna Larson and David Hager.
If you need help initiating this as some people
have then post......
Ivan
Function CountIf3D(Range3D As String, Criteria As String) _
As Variant
Dim Sheet1 As Integer
Dim Sheet2 As Integer
Dim sTestRange As String
Dim n As Integer
Dim Count As Long
Application.Volatile
If Parse3DRange(Application.Caller.Parent.Parent.Name, _
Range3D, Sheet1, Sheet2, sTestRange) = False Then
CountIf3D = CVErr(xlErrRef)
Exit Function
End If
Count = 0
For n = Sheet1 To Sheet2
With Worksheets(n)
Count = Count + Application.WorksheetFunction.CountIf( _
.Range(sTestRange), Criteria)
End With
Next n
CountIf3D = Count
End Function
'~~~~~~~~~~
Function Parse3DRange(sBook As String, SheetsAndRange _
As String, FirstSheet As Integer, LastSheet As Integer, _
sRange As String) As Boolean
Dim sTemp As String
Dim i As Integer
Dim Sheet1 As String
Dim Sheet2 As String
Parse3DRange = False
On Error GoTo Parse3DRangeError
sTemp = SheetsAndRange
i = InStr(sTemp, "!")
If i = 0 Then Exit Function
'next line will generate an error if range is invalid
'if it's OK, it will be converted to absolute form
sRange = Range(Mid$(sTemp, i + 1)).Address
sTemp = Left$(sTemp, i - 1)
i = InStr(sTemp, ":")
Sheet2 = Trim(Mid$(sTemp, i + 1))
If i > 0 Then
Sheet1 = Trim(Left$(sTemp, i - 1))
Else
Sheet1 = Sheet2
End If
'next lines will generate errors if sheet names are invalid
With Workbooks(sBook)
FirstSheet = .Worksheets(Sheet1).Index
LastSheet = .Worksheets(Sheet2).Index
'swap if out of order
If FirstSheet > LastSheet Then
i = FirstSheet
FirstSheet = LastSheet
LastSheet = i
End If
i = .Worksheets.Count
If FirstSheet >= 1 And LastSheet <= i Then
Parse3DRange = True
End If
End With
Parse3DRangeError:
On Error GoTo 0
Exit Function
End Function
Posted by Peter on November 13, 2000 4:59 AM
Ivan,
just for the record,
Thanks for your help and 'offline' time in applying the fix
rgds Peter
