Lookup on Another Worksheet & Calculate Result

I have a spreadsheet that is used for productivity tracking. It contains a basic TOTAL column. When filled out it looks like:

Date        Work Type       Amount      Total
-----       -----------      -------      -----
9/8/06      Fun work            10          40 minutes

The TOTAL column assumes that "Fun work" takes 4 minutes to complete, so 10 Fun works equals 40 minutes.

I want to be able to assign different amounts of time for different job activities. I am assuming I can create a table which acts as a legend, i.e.:

Work Type      Time      Abbr
----------      -----     -----
Fun work           4         FW
Boring work        7         BW

Then each work type would be listed with it's corresponding time alottment. Can I then take this smaller table and then, using the abbreviated codes, plug the codes into the other spreadsheet (the first one) and have it somehow know to change the formula depending on what type of work is entered?

I hope I have made sense! Thanks!

Re: Generating different products/sums by linking to other table/worksheet

Quote from eduboys

You can do exactly that!

Code

'    Col A      Col B   Col C
1) Work Type  Time   Abbr 
2) Fun work      4      FW 
3) Boring work   7      BW

If this was your table you could do a VLOOKUP to find the amount of time for the type of work.

=Vlookup("Fun Work", $A$1:$C$3,2,FALSE)

You would then need to multiply that lookup, by the units of work, in this case 10 for "Fun Work"

This formula will look up the amount of time it takes for each corresponding work type. If you wanted to list the abbreviation you could use this formula:

=Vlookup(Work Type, $A$1:$C$3,3,FALSE)

Display More

For the vlookup to work, would both tables need to be on the same worksheet? What if they are on different tabs? I have never linked tables/worksheets before, so forgive my ignorance!

Re: Generating different products/sums by linking to other table/worksheet

Where would I enter the formula? On the first table TOTAL column?

Re: Generating different products/sums by linking to other table/worksheet

Quote from Domenic

I'm a little confused. If I understand you correctly, the 'Work Type' and 'Amount' are given, and the formula needs to return the 'Total'. Is this correct? What about the abbreviated codes? Will you be using them for the 'Work Type'?

You are correct. I would like for my fellow employees to be able to use a spreadsheet, such as the very first example. As their day goes on, they would fill out the sheet based on the type of work they did.

The abbreviations would just make things easier (less typing on the spreadsheet).

Re: Generating different products/sums by linking to other table/worksheet

Awesome. Thank you, Domenic!

Can you shed a little info on the difference between vlookup and index?

Also, how can I modify the Index formula so that I would be able to easily add Work Types/abbreviations/minute values without needing to re-paste the formula in the other worksheet?

Re: Generating different products/sums by linking to other table/worksheet

Wow, it's getting better and better. One last question:

Can this work by having the named range in a whole different file than the other spreadsheet (instead of as another worksheet tab)? How would the formulas change if that's the case?

By having the smaller table in a file on it's own, my co-workers would not need to (or be able to) update the time values should there ever be a change or addition.

Thank you again for your help.