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:


    Code
    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.:


    Code
    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


    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)

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


    Assuming that F2:H4 contains your table, try...


    =INDEX($G$3:$G$4,MATCH(B2,$H$3:$H$4,0))*C2


    ...where B2 contains FW, and C2 contains 10.


    Hope this helps!

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



    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


    If the table is located on a different sheet, add the sheet name to the reference. For example, change...


    $A$1:$C$3


    to


    'Sheet1'!$A$1:$C$3

  • 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


    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'?

  • 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


    Have a look at the attached file. You'll notice that the A1:C3, on Sheet 2, contains the table, and that the following formula is entered in D2, on Sheet1, and copied down:


    =INDEX(Sheet2!$B$2:$B$3,MATCH(B2,Sheet2!$C$2:$C$3,0))*C2


    Hope this helps!

  • 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


    Quote from chrisguy

    ...Thank you, Domenic!


    You're very welcome!


    Quote

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


    Unlike INDEX/MATCH, VLOOKUP requires the lookup column to be the left-most column in the table.


    Quote

    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?


    You can use dynamic named ranges. Assuming that A1:C3 contains the table, try...


    Insert > Name > Define


    Name: Abbr


    Refers to:


    =Sheet2!$C$2:INDEX(Sheet2!$C$2:$C$65536,MATCH(REPT("z",255),Sheet2!$C$2:$C$65536))


    Click Add


    Name: Time


    Refers to:


    =Sheet2!$B$2:INDEX(Sheet2!$B$2:$B$65536,MATCH(REPT("z",255),Sheet2!$C$2:$C$65536))


    Click Ok


    Then use...


    =INDEX(Time,MATCH(B2,Abbr,0))*C2


    Hope this helps!

  • 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.

  • Re: Lookup on Another Worksheet & Calculate Result


    Change the references to include the filename in addition to the sheetname...


    Abbr:


    ='[Workbook1.xls]Sheet2'!$C$2:INDEX('[Workbook1.xls]Sheet2'!$C$2:$C$65536,MATCH(REPT("z",255),'[Workbook1.xls]Sheet2'!$C$2:$C$65536))


    Time:


    ='[Workbook1.xls]Sheet2'!$B$2:INDEX('[Workbook1.xls]Sheet2'!$B$2:$B$65536,MATCH(REPT("z",255),'[Workbook1.xls]Sheet2'!$C$2:$C$65536))


    Note that you'll need to provide the path for the file, if closed. For example...


    ='C:\Path\[Workbook1.xls]Sheet2'!$C$2:INDEX('C:\Path\[Workbook1.xls]Sheet2'!$C$2:$C$65536,MATCH(REPT("z",255),'C:\Path\[Workbook1.xls]Sheet2'!$C$2:$C$65536))


    Hope this helps!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!