Populating numerical values in adjacent cell from using a dropdown list

**up_and_under** · 06-29-2010, 09:45 PM

Hi,
I am attempting to have an estimation type worksheet for a bunch of tasks that may take different number of hours per task.
What I would like to do is have the tasks being able to be selected from a dropdown list from a separate worksheet and the amount of hours shown in an adjacent column (cell) From this I would like to total that number of hours at the bottom of the list. Any help would be appreciated

e.g.

Update document 4
Create new document 8

I know how to create the droplist, but have no idea how to populate the number(s) for each set task to the dropdown or to the worksheet to populate the data

**NBVC** · 06-29-2010, 09:58 PM

Will there be more than one number for each item in the list?

In any case, you can use SUMIF

e.g. =SUMIF('Sheet2'!$A$1:$A$10,X1,'Sheet2'!$B$1:$B$10)

this sums all the values in sheet2, B1:B10 where A1:A10 is equal to your drop down entry in X1 of the active sheet...

Adjust the ranges and sheet, cell references to suit.

If you only have one match, than number will be returned, if multiple matches, the numbers will be added... if no match, 0 is returned.

**up_and_under** · 06-29-2010, 10:27 PM

Thanks for your reply. I think we are nearly there.
I still need to be able to associate the value from the dropdown which currently doesn't have a value

Column A Column B
Update Document 4
Create Document 8

The value needs to come from the 'dropdown' worksheet where a value is associated. Currently I don't have that value associated

**NBVC** · 06-30-2010, 10:30 AM

Add the relevant associated numbers in column E of the DropDown Sheet

Then use formula:

=SUMIF(Dropdown!$D:$D,A1,Dropdown!$E:$E) in B1 of Sheet2.

Note: If you want to remove blanks from your drop down list change the Named Range Source reference to a dynamic range: e.g. for ValidAdminTasks the Refers to would be a formula: =OFFSET(Dropdown!$D$1,1,0,COUNTA(Dropdown!$D:$D)-1,1)

change others similarly...

This is a growing range.. so as you add tasks, they will get included in the drop downs.