Labor and Travel Calculator

Copper Contributor

Ok SuperUsers I need your help.  I have attached a spreadsheet that I use to track travel and labor costs but I need some help.

 

Starting with the Travel Schedule Tab: this tab is where the majority of the information comes from.  I get this list of travel dates and places by Department.  This is the raw data. I then figure out which FY it falls into based on the dates (Table H3:J7).

 

The Ref tab: This has all of the locations and per diems by location for each FY as well as the hourly rates for each person/Department/FY

 

The Tables tab is where I created the drop downs needed for the Calculator tab so this just takes the information from the Travel Schedule tab and separates them into the FY.

 

The Calculator tab is where I have to enter who is going on what trip and the dates (not always matching the travel schedule) but I need Columns I - L to change based on FY and destination. For example, what is the Rate for Germany in FY24 (based on the table in the REF tab).

 

Then the labor tab will:

                    1. Take the number of days and sort it by Person, then Department, and convert it into Hours (not including holidays/weekends). For Example, James Cameron will spend roughly 48 hours in Sales Meetings and 88 hours in HR meetings for the HR department in FY25.

                    2. Then those hours will need to be entered into a table where the 44 and 88 hours take priority and the remaining hours are distributed.  I would like the hours to be as close to 1750 as possible but can go up to 1800. (See table in K2:Q23)

 

On the Sheet3 tab, the number of hours per month will be copied and pasted into this tab, with the employees hourly wage to determine their labor cost. I hope this makes sense and someone can help me with this. The items that are bolded are where I need the help the most.  Hope you can help. Thanks. 

11 Replies

@Sbain2150 please find my solution using indirect so it will be dynamic , i called some tables in the form of FY_21 etc, then used indirect to refer to this, hope this helps

What about the labor part of it?
@chahine This will not work because I use the tables from the Tables tab to use as a drop down on the Calculator tab for the reason. So I need the following to link to the FY_21: the reason, destination and rate.
Also, would it be possible on the calculator tab, when you select the FY, it clears out the reason, the destination and the amounts? I understand this part requires VBA. Let me know.
can you explain in detail what you need so we can have a look? as i didnt understand fully your requirement

@Sbain2150 use conditional formatting, it will clear

The things that I need help with are in Bold. On the Calculations tab, when you select the FY, I want the reasons tables to be linked so that only the events that occur in that FY appear and when you select the destination it auto displays the rate for that destination and FY. On the labor tab, for the pivot table, a calculated field can be added to determine the number of hours based on the number of days traveled (excluding holidays and weekends). Then I somehow want it to automatically enter those hours for a specific FY and person to appear in a table just like it's shown in columns K2:Q23. Does this help?

@Sbain2150 hello, calculate part is now dynamic based on selection of FY 

So when I downloaded the file, everything says ref# and the reason is longer a drop-down. So it still doesn't work

@Sbain2150 its working, all dropdowns are working and values changing, dont know what is the problem in your machine 

Ok. Thank you, but you haven't addressed the labor table. I can possibly create the calculated field for the pivot table but is there any way to automatically transfer it into the table?