Drop Down List

[jtucker]

I am an absolute excel newbie but am eager to learn. I am currently trying to use excel to setup test case management. I need a drop down that appears in a column of sells that strictly brings up 3 options. They are:

Pass
Fail
Not Run

Then on the buttom of the sheet I would like for a cell to calculate a formula that would determine the amount of each one selected (i.e. 4 pass, 5 fail, etc.)

Just curious if anyone can help me.

Thank you for your help, hopefully I'll be the one responding to posts with help sometime!!!!

 

[mvptomlinson]

Hi Jtucker,

Select your cells that you want to restrict (e.g. A1:A100) and then click Data > Data Validation. In the D.V. window, on the Settings tab, select "List" in the first drop-down box. Next, click in the Source textbox and press the F2 button. Then type: Pass,Fail,Not Run

Make sure 'In-cell dropdown' is checked, as well as 'Ignore blank'. On the Input Message tab you can enter a note to tell the user something when they select one of those cells (can get annoying). On the Error Alert tab you can set warning levels if they try to enter invalid data (tough to do when you have a drop-down, but worthwhile to know).

As for counting each, at the bottom you can just use a COUNTIF formula.

=COUNTIF(A1:A100,"Pass")
=COUNTIF(A1:A100,"Fail")
etc..

 

[jtucker]

MVPTomlinson you are the man! Thank you. one other quick question...possible to change the color of the cell based upon list selection?

For example if its Pass the cell turns green
If Fail is selected it turns red
If Not Run is selected it turns amber

Thank you very much!

If you are a SD Charger Fan or LT fan (seems that you are) let me know. I run an autographed sports memorabilia company and have lots of charger and LT stuff!!!

**I am using Excel 2010**

 

[mvptomlinson]

You can change cell colors based upon input/selection using Conditional Formatting.

Select those cells (e.g. A1:A100) and then click Conditional Formatting > Highlight Cell Rules > Equal To.

In the Equal To dialog, type: Pass
In the drop-down box choose Custom Format and then choose a Green Fill color on the Fill tab in the window that appears. Click OK twice to return to the sheet.

Repeat this process for Fail and Not Run, changing the Fill color each time.

You can always manage these rules by clicking Conditional Format > Manage Rules.