Mar 17 2021 04:08 PM
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.
Mar 18 2021 12:02 AM
@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
Mar 18 2021 11:57 AM
Mar 19 2021 05:04 PM
Mar 20 2021 02:56 AM
Mar 20 2021 03:31 AM
@Sbain2150 use conditional formatting, it will clear
Mar 20 2021 10:09 AM
Mar 20 2021 08:55 PM
@Sbain2150 hello, calculate part is now dynamic based on selection of FY
Mar 20 2021 09:08 PM
Mar 21 2021 02:18 AM
@Sbain2150 its working, all dropdowns are working and values changing, dont know what is the problem in your machine
Mar 22 2021 10:39 AM