excelpowerquerypowerpivot

Excel: How to get a column of a related table into my worksheet?

I have created 3 tables in my worksheet: names, roles, costs

names:

ID NAME ROLE
1 A 1
2 B 2

roles:

ID ROLENAME SALARY
1 X 10
2 Y 50

costs:

ID NAMEID WORKEDHOURS
1 1 10
2 2 10

The tables are related to each other via Power Pivot relationships (names - role n:1 roles - id) and (names - id 1:n costs - nameid)

I want to add another calculated column to the table costs, that calculates (roles[salary]*costs[workedhours]) automatically when I enter data to the table costs:

costs:

ID NAMEID WORKEDHOURS TOTAL
1 1 10 100
2 2 10 500

In Power Pivot I added the new column "TOTAL" to costs with the formula =RELATED(role[salary])*costs[workedhours] an it works. But I want that information not only in the power pivot editor, I want to add it to my table in the worksheet, so I can see the calculated TOTAL value when I enter another row into costs.

Is there a way to achive this? Do I have to use a lookup forumla anyways? Are relationships for reports and pivot tables only?

I know that it is possible to link the whole table costs showing the column TOTAL, but then i cannot enter values...

Thanks!

Solution

  • I can propose you a solution based on Excel tables:

    enter image description here

    Fill sheets (except column "A" everywhere and column "D" on "Costs") and convert ranges to tables one by one as depicted above. Then enter formulas.

    Formula for the "A" column is the same everywhere:

    [A2]=IF(ISNUMBER(A1),A1+1,1)

    The sought formula is:

    [Costs!D2]=XLOOKUP(XLOOKUP([@NAMEID],Table2[ID],Table2[ROLE]),Table3[ID],Table3[SALARY])*[@WORKEDHOURS]

    Finally, you need to enter only NAMEID and WORKEDHOURS below Costs to get the result.