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
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
ASKER
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.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Oh, sorry, that was obviously not intentional.
Thanks for closing the question.
Good luck with the rest of your project :)
Thanks for closing the question.
Good luck with the rest of your project :)
=IF(VLOOKUP(B3,'Drop Down Selections'!F2:G18,2,FALSE
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:
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