Change cell value based on the color of another cell

W

Woodpusher147

Board Regular
Joined
Oct 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello
I hope someone can help with a (in my not super excel mind) very complicated issue.
1633520453103.png


I have the above spreadsheet to track performance which has 11 columns and however many staff there are as rows.
The sheet has conditional formating to shade each cell based on score, that's all done ok.
I also have data validation as Bedroom does not score in columns 4,5,6,7,8 - Upstairs & Downstairs do not score in 9 or 10

However, I now need to have an overall score by using the following Key
RED =0
AMBER=1
GREEN=3
YELLOW=5
The other complication is that columns 1,2,3 and 4 will score 4 times more than all other columns but I guess I can do this by having the formula/code populate hidden cell which I can then simply refer to this x4 for the cells in rows 1,2,3,4

Ive looked at an old thread which is titled "Changing cell value based on the color of another cell" and has an interesting
User Defined Function in VBA by Joe4. This could work as I dont want the code to run on all the sheet but I really need help

Rick Rothstein and sheetspread also commented and helped


I hope you can help as my head is now hurting :)


Thank you for any replies

Simon
1633519851369.png
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Are these the correct total number of points per row (column O)?

Transpose using formulas based on condition.xlsb
ABCDEFGHIJKLMNO
11234567891011
2Pointsn/a85%80%55%20%70%15%25%35%40%85%POINTS
3SimonUpstairs1854503330
4ChrisBedroom10170255041
5SimonDownstairs457468
6Upstairs10
7Bedroom0
8Downstairs6665
9Upstairs0
10Bedroom45853320
Sheet5 (2)
Cell Formulas
RangeFormula
O3:O10O3=SUMPRODUCT(CFV(D3:N3),IF(B3="Bedroom",4*FLOOR(3/(2+0.3*SEQUENCE(1,11,1)),1)+FLOOR(SEQUENCE(1,11,1)/9,1),-3*FLOOR((SEQUENCE(1,11,1,2)+2)/10,1)+4-FLOOR(SEQUENCE(1,11,1,2)/16,1)+3*FLOOR(SEQUENCE(1,11,1,2)/18,1)+FLOOR(SEQUENCE(1,11,1,2)/20,1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:N44Cell Value=6textNO
D3:N44Cell Value=74textNO
D3:N44Cell Value=70textNO
D3:N44Cell Value=50textNO
D3:N44Cell Value=33textNO
D3:N44Cell Value=50textNO
D3:N44Cell Value=25textNO
D3:N44Cell Value=85textNO
D3:N44Cell Value=101textNO
D3:N44Cell Value=45textNO
D3:N44Cell Value=4textNO
D3:N44Cell Value=1textNO
 
Upvote 0
Hi again, Simon.

Below is a video strictly for me to communicate my interpretation of what @Woodpusher147 could be asking for. (The video is strictly intended for the present, not the future. So for everyone else who want to view it in the present, that's fine, but you don't have to.)

And Simon, if my interpretation explain in the video is correct and seems to be a potential solution, both for you (and everyone else) is the code (the solution) that I showed in the video (code for the user defined function CFV() ) is shown below the video. (And of course my Excel formula is shown in the XL2BB spreadsheet capture in my previous post.)

Note that ConditionalColor (in my code at the end of this post) is a shorted/modified version I made from the (full) function from: VBA Express : Excel - Get Cell Color Function .
VBA Code: 
Function ConditionalColor(rg As Range, FormatType As String) As Long
     'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _
    FormatType Is either "Font" Or "Interior"
    Dim cel As Range
    Dim tmp As Variant
    Dim boo As Boolean
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long
    
     'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
    value of other cells
    
    Set cel = rg.Cells(1, 1)
    Select Case Left(LCase(FormatType), 1)
    Case "f" 'Font color
        ConditionalColor = cel.Font.ColorIndex
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.ColorIndex
    End Select
    
    If cel.FormatConditions.Count > 0 Then
         'On Error Resume Next
        With cel.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
                     'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead.
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
                    boo = Application.Evaluate(frmlaA1)
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                    Select Case .Item(i).Operator
                    Case xlEqual ' = x
                        frmla = cel & "=" & .Item(i).Formula1
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & .Item(i).Formula1
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
                    Case xlLess ' < x
                        frmla = cel & "<" & .Item(i).Formula1
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & .Item(i).Formula1
                    Case xlGreater ' > x
                        frmla = cel & ">" & .Item(i).Formula1
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & .Item(i).Formula1
                    End Select
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If
                
                If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    End Select
                    If Err = 0 Then ConditionalColor = tmp
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If
    
End Function

I made the below modification so that it would encompass the "= value" conditional formatting rules shown in the XL2BB spreadsheet capture from my previous post. It can be easily modified, depending on the type of conditional formatting rules that @Woodpusher147 uses in his sheet. (Which, if the code below doesn't work for him, but my assumptions shown in the video are correct, he needs to tell us.)


The code:
VBA Code: 
Option Explicit

Sub Test__CFV()
'MsgBox ConditionalColor(ActiveCell)
MsgBox CFV(ActiveCell)(1)
End Sub

Function CFV(rng As Range)

ReDim cf_Cells(1 To rng.Columns.Count)
Dim currentVal As Integer
Dim counter As Integer
counter = 1
Dim r As Range
For Each r In rng
    Select Case ConditionalColor(r)

        Case "RGB(255,0,0)" '3 'Red
            currentVal = 0

        Case "RGB(255,191,0)" '44 Amber
            currentVal = 1

        Case "RGB(0,255,0)" '4 Green
            currentVal = 3

        Case "RGB(255,255,0)" '6 Yellow
            currentVal = 5
        '***
        '(Add more Case statements here if you need to detect more conditional formatting colors.)
        '***

        Case "RGB(210,239,255)" '= -4142, Cell has no conditional formatting
            currentVal = 0
        Case Else
            currentVal = 0
    End Select
    cf_Cells(counter) = currentVal
    counter = counter + 1
Next r

CFV = cf_Cells

End Function


Sub Test__Get_This_Cells_RGB_Color()
MsgBox Get_This_Cells_RGB_Color(255)
End Sub
Function Get_This_Cells_RGB_Color(color_Constant_from_Excel As Long)
'Code is from https://www.thespreadsheetguru.com/the-code-vault/2014/11/5/retrieve-excel-cells-font-fill-rgb-color-code
'PURPOSE: Output the RGB color code for the ActiveCell's Font Color
'SOURCE: www.TheSpreadsheetGuru.com

Dim hexColor As String
hexColor = Right("000000" & Hex(color_Constant_from_Excel), 6)
Get_This_Cells_RGB_Color = "RGB(" & CInt("&H" & Right(hexColor, 2)) & "," & CInt("&H" & Mid(hexColor, 3, 2)) & "," & CInt("&H" & Left(hexColor, 2)) & ")"
End Function


Sub Test__ConditionalColor()
MsgBox ConditionalColor(ActiveCell)
End Sub
Function ConditionalColor(rg As Range)
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=190
    Dim tmp As Long
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long
    ConditionalColor = rg.Interior.ColorIndex
    If rg.FormatConditions.Count > 0 Then
        With rg.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , rg)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, rg)
                If Application.Evaluate(rg.Address & frmlaA1) Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    tmp = .Item(i).Interior.Color
                    If Err = 0 Then ConditionalColor = Get_This_Cells_RGB_Color(tmp)
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If
End Function
 
Upvote 0
And I should have thought about it earlier, but the formula can be shortend

From:
Excel Formula: 
=SUMPRODUCT(CFV(D3:N3),IF(B3="Bedroom",4*FLOOR(3/(2+0.3*SEQUENCE(1,11,1)),1)+FLOOR(SEQUENCE(1,11,1)/9,1),-3*FLOOR((SEQUENCE(1,11,1,2)+2)/10,1)+4-FLOOR(SEQUENCE(1,11,1,2)/16,1)+3*FLOOR(SEQUENCE(1,11,1,2)/18,1)+FLOOR(SEQUENCE(1,11,1,2)/20,1)))
to:
Excel Formula: 
=SUMPRODUCT(CFV(D3:N3),IF(B3="Bedroom",4*INT(3/(2+0.3*SEQUENCE(1,11,1)))+INT(SEQUENCE(1,11,1)/9),-3*INT((SEQUENCE(1,11,1,2)+2)/10)+4-INT(SEQUENCE(1,11,1,2)/16)+3*INT(SEQUENCE(1,11,1,2)/18)+INT(SEQUENCE(1,11,1,2)/20)))

I should have known that I could have avoided having a second argument of 1 if I would have used INT(a) instead of FLOOR(a,b)!
 
Upvote 0
Woodpusher147 said:
The sheet has conditional formating to shade each cell based on score, that's all done ok.
Click to expand...
Could we have details of what the conditional format rules are since they may be useful in getting to your desired results?
If possible, a good way to provide them is by posting a mini-sheet using XL2BB as cmowla has done above.


Woodpusher147 said:
However, I now need to have an overall score
Click to expand...
Unless the suggested results in the post #2 mini-sheet are correct, please include with your mini-sheet (or image if mini-sheet is not possible) the manually entered desired results for that sample data and explain how you calculated 1 or 2 of those results?
 
Upvote 0
cmowla said:
And I should have thought about it earlier, but the formula can be shortend

From:

to:
Click to expand...
Shortest possible. (I feel like an idiot, but I had fun playing with the math step functions regardless!)
Excel Formula: 
=SUMPRODUCT(CFV(D3:N3),IF(B3="Bedroom",{4,4,4,0,0,0,0,0,1,1,1},{4,4,4,4,1,1,1,1,0,0,1}))

So if the convention is correct, this formula should give no doubt of correctness.
 
Upvote 0
WOw all this looks fantastic. I have only just seen it so please give me a little while to get my head around it and I will get back with a MASSIVE thanks im sure :)


Also, I hope this is what

Peter_SSs meant​


SalesPeople Performance Tracker WORK.xlsm
FGHIJKLMNOP
618545033
710170255450
845746
9110727
10107738
1125799
12107801045
13841145
148515
159914
16100
17105
18106
19
20
21
22
Team Q3 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F6:P22Cellcontains a blank value textNO
H6:H22Cell Value<5textNO
H6:H22Cell Valuebetween 5 and 9textNO
H6:H22Cell Valuebetween 10 and 14textNO
H6:H22Cell Value>=15textNO
P16Cell Value>=89textNO
P16Cell Valuebetween 82 and 88textNO
P16Cell Valuebetween 75 and 81textNO
P16Cell Value<75textNO
O16Cell Value>=50textNO
O16Cell Valuebetween 44 and 49textNO
O16Cell Valuebetween 35 and 43textNO
O16Cell Value<35textNO
N16Cell Value>=34textNO
N16Cell Valuebetween 22 and 33textNO
N16Cell Valuebetween 20 and 21textNO
N16Cell Value<20textNO
M16Cell Value>=52textNO
M16Cell Valuebetween 36 and 51textNO
M16Cell Valuebetween 25 and 35textNO
M16Cell Value<25textNO
L16Cell Value>=32textNO
L16Cell Valuebetween 23 and 31textNO
L16Cell Valuebetween 18 and 22textNO
L16Cell Value<18textNO
K16Cell Value>=85textNO
K16Cell Valuebetween 76 and 84textNO
K16Cell Valuebetween 70 and 75textNO
K16Cell Value<70textNO
J16Cell Value>=30textNO
J16Cell Valuebetween 24 and 29textNO
J16Cell Valuebetween 20 and 23textNO
J16Cell Value<21textNO
G6:G22Cell Value<70textNO
G6:G22Cell Valuebetween 70 and 74textNO
I16Cell Value>75textNO
I16Cell Valuebetween 65 and 75textNO
I16Cell Valuebetween 58 and 64textNO
I16Cell Value<58textNO
G6:G22Cell Valuebetween 75 and 79textNO
G6:G22Cell Value>=80textNO
F6:F22Cell Value>105textNO
F6:F22Cell Valuebetween 100 and 105textNO
F6:F22Cell Valuebetween 85 and 99textNO
F6:F22Cell Value<85textNO
P21Cell Value>=89textNO
P21Cell Valuebetween 82 and 88textNO
P21Cell Valuebetween 75 and 81textNO
P21Cell Value<75textNO
O21Cell Value>=50textNO
O21Cell Valuebetween 44 and 49textNO
O21Cell Valuebetween 35 and 43textNO
O21Cell Value<35textNO
N21Cell Value>=34textNO
N21Cell Valuebetween 22 and 33textNO
N21Cell Valuebetween 20 and 21textNO
N21Cell Value<20textNO
M21Cell Value>=52textNO
M21Cell Valuebetween 36 and 51textNO
M21Cell Valuebetween 25 and 35textNO
M21Cell Value<25textNO
L21Cell Value>=32textNO
L21Cell Valuebetween 23 and 31textNO
L21Cell Valuebetween 18 and 22textNO
L21Cell Value<18textNO
K21Cell Value>=85textNO
K21Cell Valuebetween 76 and 84textNO
K21Cell Valuebetween 70 and 75textNO
K21Cell Value<70textNO
J21Cell Value>=30textNO
J21Cell Valuebetween 24 and 29textNO
J21Cell Valuebetween 20 and 23textNO
J21Cell Value<21textNO
I21Cell Value>75textNO
I21Cell Valuebetween 65 and 75textNO
I21Cell Valuebetween 58 and 64textNO
P9Cell Value>=89textNO
P9Cell Valuebetween 82 and 88textNO
P9Cell Valuebetween 75 and 81textNO
P9Cell Value<75textNO
O9Cell Value>=50textNO
O9Cell Valuebetween 44 and 49textNO
O9Cell Valuebetween 35 and 43textNO
O9Cell Value<35textNO
N9Cell Value>=34textNO
N9Cell Valuebetween 22 and 33textNO
N9Cell Valuebetween 20 and 21textNO
N9Cell Value<20textNO
M9Cell Value>=52textNO
M9Cell Valuebetween 36 and 51textNO
M9Cell Valuebetween 25 and 35textNO
M9Cell Value<25textNO
L9Cell Value>=32textNO
L9Cell Valuebetween 23 and 31textNO
L9Cell Valuebetween 18 and 22textNO
L9Cell Value<18textNO
K9Cell Value>=85textNO
K9Cell Valuebetween 76 and 84textNO
K9Cell Valuebetween 70 and 75textNO
K9Cell Value<70textNO
J9Cell Value>=30textNO
J9Cell Valuebetween 24 and 29textNO
J9Cell Valuebetween 20 and 23textNO
J9Cell Value<21textNO
I9Cell Value>75textNO
I9Cell Valuebetween 65 and 75textNO
I9Cell Valuebetween 58 and 64textNO
I9Cell Value<58textNO
P17:P18Cell Value>=89textNO
P17:P18Cell Valuebetween 82 and 88textNO
P17:P18Cell Valuebetween 75 and 81textNO
P17:P18Cell Value<75textNO
O17:O18Cell Value>=50textNO
O17:O18Cell Valuebetween 44 and 49textNO
O17:O18Cell Valuebetween 35 and 43textNO
O17:O18Cell Value<35textNO
N17:N18Cell Value>=34textNO
N17:N18Cell Valuebetween 22 and 33textNO
N17:N18Cell Valuebetween 20 and 21textNO
N17:N18Cell Value<20textNO
M17:M18Cell Value>=52textNO
M17:M18Cell Valuebetween 36 and 51textNO
M17:M18Cell Valuebetween 25 and 35textNO
M17:M18Cell Value<25textNO
L17:L18Cell Value>=32textNO
L17:L18Cell Valuebetween 23 and 31textNO
L17:L18Cell Valuebetween 18 and 22textNO
L17:L18Cell Value<18textNO
K17:K18Cell Value>=85textNO
K17:K18Cell Valuebetween 76 and 84textNO
K17:K18Cell Valuebetween 70 and 75textNO
K17:K18Cell Value<70textNO
J17:J18Cell Value>=30textNO
J17:J18Cell Valuebetween 24 and 29textNO
J17:J18Cell Valuebetween 20 and 23textNO
J17:J18Cell Value<21textNO
I17:I18Cell Value>75textNO
I17:I18Cell Valuebetween 65 and 75textNO
I17:I18Cell Valuebetween 58 and 64textNO
I17:I18Cell Value<58textNO
I21Cell Value<58textNO
P22Cell Value>=89textNO
P22Cell Valuebetween 82 and 88textNO
P22Cell Valuebetween 75 and 81textNO
O22Cell Value>=50textNO
O22Cell Valuebetween 44 and 49textNO
O22Cell Valuebetween 35 and 43textNO
O22Cell Value<35textNO
N22Cell Value>=34textNO
N22Cell Valuebetween 22 and 33textNO
N22Cell Valuebetween 20 and 21textNO
N22Cell Value<20textNO
M22Cell Value>=52textNO
M22Cell Valuebetween 36 and 51textNO
M22Cell Valuebetween 25 and 35textNO
M22Cell Value<25textNO
L22Cell Value>=32textNO
L22Cell Valuebetween 23 and 31textNO
L22Cell Valuebetween 18 and 22textNO
L22Cell Value<18textNO
K22Cell Value>=85textNO
K22Cell Valuebetween 76 and 84textNO
K22Cell Valuebetween 70 and 75textNO
K22Cell Value<70textNO
J22Cell Value>=30textNO
J22Cell Valuebetween 24 and 29textNO
J22Cell Valuebetween 20 and 23textNO
J22Cell Value<21textNO
I22Cell Value>75textNO
I22Cell Valuebetween 65 and 75textNO
I22Cell Valuebetween 58 and 64textNO
I22Cell Value<58textNO
P13:P14Cell Value>=89textNO
P13:P14Cell Valuebetween 82 and 88textNO
P13:P14Cell Valuebetween 75 and 81textNO
P13:P14Cell Value<75textNO
O13:O14Cell Value>=50textNO
O13:O14Cell Valuebetween 44 and 49textNO
O13:O14Cell Valuebetween 35 and 43textNO
O13:O14Cell Value<35textNO
N13:N14Cell Value>=34textNO
N13:N14Cell Valuebetween 22 and 33textNO
N13:N14Cell Valuebetween 20 and 21textNO
N13:N14Cell Value<20textNO
M13:M14Cell Value>=52textNO
M13:M14Cell Valuebetween 36 and 51textNO
M13:M14Cell Valuebetween 25 and 35textNO
M13:M14Cell Value<25textNO
L13:L14Cell Value>=32textNO
L13:L14Cell Valuebetween 23 and 31textNO
L13:L14Cell Valuebetween 18 and 22textNO
L13:L14Cell Value<18textNO
K13:K14Cell Value>=85textNO
K13:K14Cell Valuebetween 76 and 84textNO
K13:K14Cell Valuebetween 70 and 75textNO
K13:K14Cell Value<70textNO
J13:J14Cell Value>=30textNO
J13:J14Cell Valuebetween 24 and 29textNO
J13:J14Cell Valuebetween 20 and 23textNO
J13:J14Cell Value<21textNO
I13:I14Cell Value>75textNO
I13:I14Cell Valuebetween 65 and 75textNO
I13:I14Cell Valuebetween 58 and 64textNO
I13:I14Cell Value<58textNO
P12Cell Value>=89textNO
P12Cell Valuebetween 82 and 88textNO
P12Cell Valuebetween 75 and 81textNO
P12Cell Value<75textNO
O12Cell Value>=50textNO
O12Cell Valuebetween 44 and 49textNO
O12Cell Valuebetween 35 and 43textNO
O12Cell Value<35textNO
N12Cell Value>=34textNO
N12Cell Valuebetween 22 and 33textNO
N12Cell Valuebetween 20 and 21textNO
N12Cell Value<20textNO
M12Cell Value>=52textNO
M12Cell Valuebetween 36 and 51textNO
M12Cell Valuebetween 25 and 35textNO
M12Cell Value<25textNO
L12Cell Value>=32textNO
L12Cell Valuebetween 23 and 31textNO
L12Cell Valuebetween 18 and 22textNO
L12Cell Value<18textNO
K12Cell Value>=85textNO
K12Cell Valuebetween 76 and 84textNO
K12Cell Valuebetween 70 and 75textNO
K12Cell Value<70textNO
J12Cell Value>=30textNO
J12Cell Valuebetween 24 and 29textNO
J12Cell Valuebetween 20 and 23textNO
J12Cell Value<21textNO
I12Cell Value>75textNO
I12Cell Valuebetween 65 and 75textNO
I12Cell Valuebetween 58 and 64textNO
I12Cell Value<58textNO
P7Cell Value>=89textNO
P7Cell Valuebetween 82 and 88textNO
P7Cell Valuebetween 75 and 81textNO
P7Cell Value<75textNO
O7Cell Value>=50textNO
O7Cell Valuebetween 44 and 49textNO
O7Cell Valuebetween 35 and 43textNO
O7Cell Value<35textNO
N7Cell Value>=34textNO
N7Cell Valuebetween 22 and 33textNO
N7Cell Valuebetween 20 and 21textNO
N7Cell Value<20textNO
M7Cell Value>=52textNO
M7Cell Valuebetween 36 and 51textNO
M7Cell Valuebetween 25 and 35textNO
M7Cell Value<25textNO
L7Cell Value>=32textNO
L7Cell Valuebetween 23 and 31textNO
L7Cell Valuebetween 18 and 22textNO
L7Cell Value<18textNO
K7Cell Value>=85textNO
K7Cell Valuebetween 76 and 84textNO
K7Cell Valuebetween 70 and 75textNO
K7Cell Value<70textNO
J7Cell Value>=30textNO
J7Cell Valuebetween 24 and 29textNO
J7Cell Valuebetween 20 and 23textNO
J7Cell Value<21textNO
I7Cell Value>75textNO
I7Cell Valuebetween 65 and 75textNO
I7Cell Valuebetween 58 and 64textNO
I7Cell Value<58textNO
P19:P20,P15,P10:P11,P8,P6Cell Value>=89textNO
P19:P20,P15,P10:P11,P8,P6Cell Valuebetween 82 and 88textNO
P19:P20,P15,P10:P11,P8,P6Cell Valuebetween 75 and 81textNO
P19:P20,P15,P10:P11,P8,P6Cell Value<75textNO
O19:O20,O15,O10:O11,O8,O6Cell Value>=50textNO
O19:O20,O15,O10:O11,O8,O6Cell Valuebetween 44 and 49textNO
O19:O20,O15,O10:O11,O8,O6Cell Valuebetween 35 and 43textNO
O19:O20,O15,O10:O11,O8,O6Cell Value<35textNO
N19:N20,N15,N10:N11,N8,N6Cell Value>=34textNO
N19:N20,N15,N10:N11,N8,N6Cell Valuebetween 22 and 33textNO
N19:N20,N15,N10:N11,N8,N6Cell Valuebetween 20 and 21textNO
N19:N20,N15,N10:N11,N8,N6Cell Value<20textNO
M19:M20,M15,M10:M11,M8,M6Cell Value>=52textNO
M19:M20,M15,M10:M11,M8,M6Cell Valuebetween 36 and 51textNO
M19:M20,M15,M10:M11,M8,M6Cell Valuebetween 25 and 35textNO
M19:M20,M15,M10:M11,M8,M6Cell Value<25textNO
L19:L20,L15,L10:L11,L8,L6Cell Value>=32textNO
L19:L20,L15,L10:L11,L8,L6Cell Valuebetween 23 and 31textNO
L19:L20,L15,L10:L11,L8,L6Cell Valuebetween 18 and 22textNO
L19:L20,L15,L10:L11,L8,L6Cell Value<18textNO
K19:K20,K15,K10:K11,K8,K6Cell Value>=85textNO
K19:K20,K15,K10:K11,K8,K6Cell Valuebetween 76 and 84textNO
K19:K20,K15,K10:K11,K8,K6Cell Valuebetween 70 and 75textNO
K19:K20,K15,K10:K11,K8,K6Cell Value<70textNO
J19:J20,J15,J10:J11,J8,J6Cell Value>=30textNO
J19:J20,J15,J10:J11,J8,J6Cell Valuebetween 24 and 29textNO
J19:J20,J15,J10:J11,J8,J6Cell Valuebetween 20 and 23textNO
J19:J20,J15,J10:J11,J8,J6Cell Value<21textNO
I19:I20,I15,I10:I11,I8,I6Cell Value>75textNO
I19:I20,I15,I10:I11,I8,I6Cell Valuebetween 65 and 75textNO
I19:I20,I15,I10:I11,I8,I6Cell Valuebetween 58 and 64textNO
I19:I20,I15,I10:I11,I8,I6Cell Value<58textNO
P22Cell Value<75textNO
Cells with Data Validation
CellAllowCriteria
F6:H22Any value
I6:M22Custom=$D6<>"Bedroom"
N6:O22Custom=$D6="Bedroom"
P6:P22Any value
 
Upvote 0
The column with Bedroom/Upstairs and Downstairs and a simple dropdown list for the user to choose which department so will always be spelt exactly the same.

I used data validation to prevent bedroom from entering data into 4,5,6,7,8 and the same for Downstairs /Upstairs in 9 and 10

Thank you so much for the help
 
Upvote 0
cmowla said:
Are these the correct total number of points per row (column O)?

Transpose using formulas based on condition.xlsb
ABCDEFGHIJKLMNO
11234567891011
2Pointsn/a85%80%55%20%70%15%25%35%40%85%POINTS
3SimonUpstairs1854503330
4ChrisBedroom10170255041
5SimonDownstairs457468
6Upstairs10
7Bedroom0
8Downstairs6665
9Upstairs0
10Bedroom45853320
Sheet5 (2)
Cell Formulas
RangeFormula
O3:O10O3=SUMPRODUCT(CFV(D3:N3),IF(B3="Bedroom",4*FLOOR(3/(2+0.3*SEQUENCE(1,11,1)),1)+FLOOR(SEQUENCE(1,11,1)/9,1),-3*FLOOR((SEQUENCE(1,11,1,2)+2)/10,1)+4-FLOOR(SEQUENCE(1,11,1,2)/16,1)+3*FLOOR(SEQUENCE(1,11,1,2)/18,1)+FLOOR(SEQUENCE(1,11,1,2)/20,1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:N44Cell Value=6textNO
D3:N44Cell Value=74textNO
D3:N44Cell Value=70textNO
D3:N44Cell Value=50textNO
D3:N44Cell Value=33textNO
D3:N44Cell Value=50textNO
D3:N44Cell Value=25textNO
D3:N44Cell Value=85textNO
D3:N44Cell Value=101textNO
D3:N44Cell Value=45textNO
D3:N44Cell Value=4textNO
D3:N44Cell Value=1textNO
Click to expand...
THat looks great, Row 8 and 10 wouldn't be able to input anything into column L or H respectively due to the data validation rules but your calculation is spot on
 
Upvote 0
Basically, the Key for the conditional formatting is:
Red > Below acceptable standards.​
less than 85%​
less than 70%​
less than 5​
less than 65% ​
less than 20%​
less than 75% ​
less than 20%​
less than 20%​
less than 35%​
less than 50%​
less than 80%​
COLUMN
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
Amber > Improvement needed to meet acceptable standards.​
85% -99%​
70-74%​
5+​
65% - 69% ​
20% - 23%​
75% - 79% ​
21% - 25% ​
21% - 25% (20% - 21%​
35% - 39% ​
50% - 64% ​
80% - 84% (75% - 81%​
Green > Meets acceptable standards.​
100%-104%​
75-79%​
10+​
70% - 74% ​
24% - 29%​
80 - 84% ​
26% - 30% (23% - 31%​
26% - 30% ​
40% - 59% ​
65% - 74% ​
85% - 89% ​
Gold> Outstanding​
Over 105%​
80% and over​
15+​
75% and over​
30% and over​
85% and over​
30% and over​
30% and over ​
60% and over ​
75% and over ​
90% and over ​
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Help with SUMPRODUCT formula
Replies
3
Views
308
Woodpusher147
W
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
519
Fluff
Fluff
M
  • Question Question
Calculating from matrix cells
Replies
1
Views
142
Fluff
Fluff
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
161
Cubist
Cubist
N
  • Solved
Totaling colored cells by their value
2
Replies
17
Views
464
nsipes
N

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top