Forum Discussion
Advanced Logical Linking Formulas in Excel
I have regenerated an older tracker that my company uses to track driver loads. My higher headquarters requires redundant reporting so rather than fill out multiple trackers that ask for the same data in different formats and ways, I am trying to link everything from one source.
I have completed all my conditional formatting in the tracker and am trying to make the spreadsheet "think". I have to get the tracker in the bottom center / right of this image to pull data from the tracker at the top. The problem is, for instance, cell S37 needs to pull the route number from whichever load is the current load (the driver could have loads on M, W, & F; and if all 3 of those dates have load data, I need the spreadsheet to chose (logically) the current load he / she is on from AK5:AK34 or AU5:AU34 or BE5:AU34..... and so on). I am lost trying to make sure the proper data is displayed.
Any help would be greatly appreciated.
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 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.
- 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.
It sounds like you need to create a formula that dynamically selects the route number based on the current date. You can achieve this using a combination of functions like INDEX, MATCH, and logical functions like IF.
Here is a basic example of how you can approach this:
Assuming your dates are in column A and route numbers are in column B, and your current date is in cell S1, you can use the following formula in cell S37:
=IFERROR(INDEX($B$5:$B$34, MATCH(TODAY(), $A$5:$A$34, 0)), "")
This formula will search for the current date (TODAY()) in the range A5:A34 and return the corresponding route number from the range B5:B34. If the current date is not found, it will return an empty string ("").
You may need to adjust the cell references and ranges based on your actual data layout and requirements. Additionally, if you have multiple dates for the current day, you may need to refine the logic further to determine which route number to select.
Feel free to provide more specific details about your data layout and any additional criteria for selecting the current load. The text and steps was created with the help of AI.
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
- I will be applying this to see if it works; it sounds like at least partially what I am trying to do. I will update once I have had the opportunity to input, tweak, and test it. Thank you.
- DELIVERY DATES D1:J1
DELIVERY DAY OF THE WEEK D3:J3
ROUTE NUMBER D5:D34 (SUNDAY)
E5:E34 (MONDAY)
F5:F34 (TUESDAY)
G5:G34 (WEDNESDAY)
H5:H34 (THURSDAY)
I5:I34 (FRIDAY)
J5:J34 (SUNDAY)
CURRENT DATE NOT DISPAYED CURRENTLY
I am trying to make the most current data from the cell areas above (N5:AB35) automatically be populated in the cell area at the bottom (R37:AD66).
I would share the spreadsheet but I cannot upload it here; on a picture.you can use onedrive to share things on here so everyone can help you troubleshoot things expeditiously.
