Excel Conditional Format on Dynamic Drop-Down List

Hello -

Hoping someone can assist with the following.  Based on the attached sample I need to get cell B12 on the Assessment tool worksheet to indicate Pass or Fail (with conditional formatting of Green or Red) based on the total sum of scores (D12) being equal to or higher then the cut scores on worksheet 'Drop Down Selections' column G.
On the first worksheet referenced there is a drop-down list, B3, to change the job title.  So I need the Pass/Fail to work based on the cut score of the job title selected.

If there is a way to indicate the score from the first sheet cell D12 next to the job title in the drop down selection worksheet (column I) that would be great if it can be included.

Any assistance would be greatly appreciated.
EE-Sample.xlsm
LVL 1
EscanabaManager - HR AnalyticsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
I have placed the following formula in cell [B12] of the [Assessment Tool] worksheet:

=IF(VLOOKUP(B3,'Drop Down Selections'!F2:G18,2,FALSE)<D12,"FAIL","PASS")

I have also added two Conditional Formatting rules so that if...

cell [B12] is "PASS", the cell background colour is GREEN, or
cell [B12] is "FAIL", the cell background colour is RED.

Please see the attached workbook.

However, sorry, this sentence is confusing me:
If there is a way to indicate the score from the first sheet cell D12 next to the job title in the drop down selection worksheet (column I) that would be great if it can be included.

Do you change the values in the range [D6:D10] in the [Assessment Tool] worksheet?

If so, is it the revised total (following amendment) in cell [D12] that needs to be displayed on the [Drop Down Selections] worksheet (in the corresponding column [ I ]) when the "Job Title" in cell [B3] of the [Assessment Tool] worksheet is changed?
Q-28705735.xlsm
EscanabaManager - HR AnalyticsAuthor Commented:
Sorry for the confusion - to answer your question, yes the values in the D6:D10 range will change.  What I was looking for is taking the total that appears in cell D12 on the assessment tool worksheet and have it placed on the same job title row in the drop down selection worksheet (column I). For example, if Pilot was the selected job title, then the score in cell D12 would appear in cell I3 in the drop down selection worksheet.  If Editior was selected it would appear in cell I14, etc. etc.
[ fanpages ]IT Services ConsultantCommented:
Thanks.  That is what I thought you meant, but wanted to make sure I understood correctly.

What triggers the value in [D12] (of [Assessment Tool] being transposed to column [ I ] on [Drop Down Selections]?

Is this just the re-selection from the drop-down list in cell [B3], or are you planning to implement a button to "Save" the change?  Alternatively, does simply a change to cell [D12] automatically move to the appropriate row in column [ I ] of the other worksheet?

Thanks for your clarification.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

EscanabaManager - HR AnalyticsAuthor Commented:
Just the re-selection of the job title (B3).  Ideally the change in job title would change the row where the assessment total would be placed in column I on the other worksheet.
[ fanpages ]IT Services ConsultantCommented:
OK...

Please review this updated workbook (attached) to see if it suits your requirements.

I have changed the Worksheet_Change() event code within the [Asssessment Tool] worksheet as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim objFind                                           As Range                                                                                        ' *** fanpages [14/08/2015]: Added
  
  If Target.Row = 3 And Target.Column = 2 Then
     If Len(Trim$(strJob_Title)) > 0 Then                                                                                                               ' *** fanpages [14/08/2015]: Added
        Set objFind = Worksheets("Drop Down Selections").Columns("F").Find(What:=strJob_Title)                                                          ' *** fanpages [14/08/2015]: Added
          
        If Not (objFind Is Nothing) Then                                                                                                                ' *** fanpages [14/08/2015]: Added
           objFind.Offset(, 3).Value = [D12].Value                                                                                                      ' *** fanpages [14/08/2015]: Added
        End If ' If Not (objFind Is Nothing) Then                                                                                                       ' *** fanpages [14/08/2015]: Added
     End If ' If Len(Trim$(strJob_Title)) > 0 Then                                                                                                      ' *** fanpages [14/08/2015]: Added
       
     Range("B6:B10").ClearContents
     
     lastRow = Sheets("Drop Down Selections").Cells(Rows.Count, "D").End(xlUp).Row
     
     j = 6
        
     For i = 2 To lastRow
         If Sheets("Drop Down Selections").Cells(i, 3) <> "" Then
            v = Sheets("Drop Down Selections").Cells(i, 3)
         End If
         If v = Target.Value Then
            Cells(j, 2) = Sheets("Drop Down Selections").Cells(i, 4)
            j = j + 1
         End If
     Next
        
     strJob_Title = Target.Value                                                                                                                        ' *** fanpages [14/08/2015]: Added
  End If
    
  Set objFind = Nothing                                                                                                                                 ' *** fanpages [14/08/2015]: Added
  
End Sub

Open in new window



I have also added some code to the (This)Workbook code module (that I have named "wbkQ_28705735"):

Option Explicit
Private Sub Workbook_Open()

  strJob_Title = Worksheets("Assessment Tool").Cells(3&, "B").Value                                                                                     ' *** fanpages [14/08/2015]: Added
  
End Sub

Open in new window



Finally, I have added a new code module, "basQ_28705735":

Option Explicit

' [ http://www.experts-exchange.com/questions/28705735/Excel-Conditional-Format-on-Dynamic-Drop-Down-List.html ]

Public strJob_Title                                     As String                                                                                       ' *** fanpages [14/08/2015]: Added

Open in new window

Q-28705735b.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaManager - HR AnalyticsAuthor Commented:
The sum was not lining up with the job titles but I see the direction you're going in and can adjust.  Thank you for all of your efforts!
[ fanpages ]IT Services ConsultantCommented:
Oh, sorry, that was obviously not intentional.

Thanks for closing the question.

Good luck with the rest of your project :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.