Forum Discussion

lonnykoons's avatar
lonnykoons
Copper Contributor
Apr 10, 2024

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 dat...
excel
Excel for web
Formulas and Functions
office 365
lonnykoons's avatar
lonnykoons
Copper Contributor
Apr 11, 2024
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).
Yea_So's avatar
Yea_So
Bronze Contributor
Apr 11, 2024

lonnykoons 

 

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.

 

  • lonnykoons's avatar
    lonnykoons
    Copper Contributor
    Apr 11, 2024
    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?
    • Yea_So's avatar
      Yea_So
      Bronze Contributor
      Apr 11, 2024

      lonnykoons 

       

      In excel you can do data modeling by making connections between tables:

       

      This is why I am suggesting to create your tables in separate sheets because your tables will grow as time goes by, it's easier to archive or look for things or organize data

Resources