Forum Discussion
Advanced Logical Linking Formulas in Excel
It looks like a big complicated project, however the reason why you're lost trying to make sure the proper data is displayed is because you don't have any.
Where is the loads data?
When I say where the load data is, I mean the raw data.
For example:
You get a load, what are it's attributes?
Load Number
Origin
Destination
Date received
Target delivery date
Target pick up date
Miles
Drop and Hook?
Live load?
Live unload?
Lumpers?
etc. etc.
Where are the load details because these are the data that you are lost trying to display because you don't have any.
Create the Loads table first.
Start there, then you can figure out what other how to move the data in the load table into another table that you will create follow the manual procedure first before you can automate the process to make it efficient. Stop focusing on formatting and beautiful it is going to look and focus instead on gathering the data and the process logic.
You already know the process, and possibly the load data the very thing that you are going to play with the system that you are trying to build.
- The load data is physically inputted into this spreadsheet from individual sheets of paper handed to us by the Family Dollar Transportation Office every morning. There is no spreadsheet prior to this one.
- The raw data is delivered to me every morning via paper copy and pdf emailed copies. I then put it in the blanks in the top right (for each day). The fields are purposefully blank but I could add anything in at any time which will populate on the left side of the top spreadsheet. I am trying to get it do also move to the bottom spreadsheet which is more complex because it needs to automatically pull the most current data from the top right spreadsheet (for each day).
Beautiful, you can populate your tables by using
1. power query by pdf
2. By image
Copy the image then
You can start creating your tables (make sure you make your tables in separate sheets)
These tables are your source datasets that you can pull relevant information into your tracking sheet using either power query> data model > power pivot or via formulas and functions (ie vlookup, index match etc)
start making your tables:
Loads table
Planning table
Dispatch table
Trip table
Driver table
Truck table
Billing table
etc.
Once you've created those tables and populated them, then you can start focusing on the logic of walking those dataset from one table to the next table using power query, formulas, functions etc.
- Ok, lots to figure out here. I need to keep all my tables on one sheet; this sheet is renamed and saved each week using a fresh blank copy for the CURRENT week. Can I do this without creating separate sheets for each table?
