## Referencing a table and returning the result

Discuss the spreadsheet application

### Referencing a table and returning the result

Hi everyone.

Any help is gratefully appreciated...

Basically Im trying to provide (yet another) management report on resources.
Each Agent is split between voice and admin duties and so I have a table where this is dictated (see Sheet "Control"cells A1-C11) in percentage splits 70/30 or 30/70 etc.

Table for Agent splits per area

The Sheet Report very simply takes the Agents hours worked and multiplies that by the appropriate percentage split, resulting in hours worked in each area (Vocie and Admin).

Report

My problem is I havent a clue how to take the Agents level from the "Report" sheet in column D, look it up in the table (sheet "Control" cells A1-C11) and pass that back to the report for the calculation of hours worked in that area.

Critical to this setup, is that the Sheet "Control" is where we make changes to Agent names/levels etc.

I thought I could use INDIRECT with SUBSTITUTE but quickly became confused...

Essentially as I see it the formula in Sheet "Report" column E (and F) has to take the Agent level from column D, reference the table in Sheet "Control" to ascertain the percentage split and return the value to the cell. Then Columns F and H are simple calculations (percentage x hours).
tester 1.ods
Table references for calculations

...of course this is how my tiny brain solves the issue but I am of course open to your suggestions.
Any help appreciated as always.
Cheers.
P
OpenOffice 3.1 on Windows Vista
pherriot

Posts: 54
Joined: Wed Mar 27, 2019 1:06 pm

### Re: Referencing a table and returning the result

The formula for E2 is
Code: Select all   Expand viewCollapse view
`=VLOOKUP(D2;Control.\$A\$2:\$B\$7;2;0)`

and the formula for G2is
Code: Select all   Expand viewCollapse view
`=VLOOKUP(D2;Control.\$A\$2:\$C\$7;3;0)`

You will also want to change the format of columns F and H to display as hours instead of plain numbers, which are the fraction of a day.
Keep in mind that the value of columns F and H will be in units of Days, regardless of the formatting. Eight hours has the value 0.333. If you will use these cells to calculate costs, you will need to adjust your formulas accordingly.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator

Posts: 7260
Joined: Sat Nov 08, 2008 8:08 pm