Changing values based on drop down menu selection

**henro8** · 12-15-2009, 10:09 PM

Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis.
The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16.
For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16.
If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.

Thank you, preemptively, for any help with this problem.

**teylyn** · 12-15-2009, 10:47 PM

Hi henro8,

a simple SUMIF should do the trick,

=SUMIF($A$7:$A$16,$A19,F7:F16)

Format the result with custom format [hh]:mm

you may want to get rid of the #Value! errors by encasing the time calculation in an IFERROR()

=IFERROR(E7-B7,"")

hth

**henro8** · 12-15-2009, 11:36 PM

thanks so much teylyn! that was all a huge help, and much more simple than i had anticipated.

**jenhern6** · 07-13-2010, 03:41 PM

Hello,

I am trying to create a schedule in which instead of putting a shift such as "8 am-4 pm" in a cell, I choose from a drop down menu either full shift AM, Full shift PM, etc. I want to assign the total number of hours worked per shift to each drop down menu option. Ex: full day AM= 8 am-4 pm so it would equal 8 hrs worked. I then want to add all these hours worked per day to make a weekly total. Ex: Monday 8 hrs, Tues 6 hrs= 14 hrs weekly.

Can you please guide me to the proper equation to do this? I tried using the Lookup function but would constantly get errors such as #Value!, or #Name?, #Ref?, or N/A.

I have attached the template I am working on.

Thanks in advanced.

**teylyn** · 07-14-2010, 03:47 AM

Jenhern,
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.