excel drop down help

[AHarvey]

Hi all,

We have a 2007 excel spreadsheet at work that we use to do quality checks, we need to amend it but the person that created it has left.

it looks like this:

What we do, is select from a drop down list either YES, NO or N/A. At the bottom is a % score that uses the following formula:

=IF(D14="No","FAIL",IF(D15="No","Fail",(SUM(E8:E15))))

The problem I am having is that I have no idea how to have a drop down list like those shown that for instance, when you select YES will show YES in the cell but also assign it a score so that it can be totalled up.

Any advice?

EDIT: Just found a hidden column that checks what is in the drop down list and then assigns a value so I can now do that. Just need to know how to insert a drop down list?

 

[roadie]

I think the function is called Filter?

 

[AHarvey]

I've found a load of more hidden cells so I should be able to figure this out, turns out I need to use Data Validation and then select LIST from the options.

Although I may be back with advice on a formula later

 

[Yakyb]

do some research on named ranges they make using the list function very easy