Resolved - Need help with spread sheet

[Ultranator BAR]

Name: Ultranator

Rank: Awesome

Type of issue: (Software)

Brief Description of Issue:

I'm trying to use Excel 2010 to track how my managers are meeting their drive thru time goals.

I've set up columns for goal and actual times for 5 parts of the day, and then later assign the manager who was in charge of that day part.

I've figured out how to make a drop down box wit the managers names. (There are 13 total)

What I'd like to be able to do is have the spread sheet subtract the actual time from the goal time, and then add that to a running tally of the manager I assigned to a shift.

Is there a way to use the drop down list to make variables determined by what the name is?

So I've got like:

|                 Drive Thru Times                   |   

|Morning        |   Lunch         |  Dinner        |    Manager                         |

|goal | Actual | goal | Actual | goal | Actual |  Morning | Lunch  | Dinner |

| 140 | 142   |  130  |  99    |  145 | 160    |  Bob       |  Sue      |  Joe    |

How do I make it so that Bob would have a value of -2 for the day, Sue 31, and Joe -25?

And then be able to keep track of what Bob, Sue, and the other 13 managers do for a Month or so?

***Medical Supply Staff ONLY Below this line***

Current Status: Resolved

Main Technician:

Supporting Technician:

Edited July 31, 2013 by Holland 1st MRB

[Lafy BAR]

Because of the way you've set it up with morning, lunch, dinner, you're going to have to do things 3 times, but that shouldn't be a big deal.

I assume that you used a 2nd sheet to make the drop down work (i hope), if you didn't then switch the source of your drop down from typed in names to the first column of another sheet.

On that sheet you're going to have 4 more columns, one for each shift and the actual total.

Manager | M    | L     | D    | Total

Joe         | 10  |0      | 66   | 76

Sue        | 342 |40    | 120 | 502

Bob        | -30  |200 | 22   | 192

The code in M/L/D is almost identical, you're just going to reference the correct actual/goal columns for each. Instead of finding the difference and then adding them, i'm going to difference the sums because the SumIf command will be helpful.

=sumif(Sheet1!G:G,A2,Sheet1!A:A)-sumif(Sheet1!G:G,A2,Sheet1!B:B)

=sumif(Sheet1!H:H,A2,Sheet1!C:C)-sumif(Sheet1!G:G,A2,Sheet1!D:D)

=sumif(Sheet1!I:I,A2,Sheet1!E:E)-sumif(Sheet1!G:G,A2,Sheet1!F:F)

And the code for total is simple

=B2+C2+D2

Then you obviously just drag the formulas down

If you have any trouble with any of this just find me on vent and i'll give you a hand.

[Ultranator BAR]

Now that I have time to work on this, I realized that my example of my spreadsheet was a bit generic.

Here's my spreadsheet:

http://www.mediafire.com/?q3p960vpq3svcr0

I actually have 5 to 6 day parts, and the third shift is only there on Thurs, Fri, and Sat. I thought I would be able to translate the formulas into them, but it's a bit more complicated then I'm used to.

[Ultranator BAR]

Lafy and I worked it out tonight.

Thanks, this can be closed now.