Referencing a table and returning the result

Discuss the spreadsheet application

Referencing a table and returning the result

Postby pherriot » Wed May 15, 2019 11:40 am

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.

Captura.JPG
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).

Captura2.JPG
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
(23.01 KiB) Downloaded 14 times

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

Postby FJCC » Wed May 15, 2019 2:45 pm

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: 7257
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests