Forum Discussion

AGGP2022's avatar
AGGP2022
Copper Contributor
Nov 29, 2022

Auto Fill Data Formula

I am doing a lane analysis for freight work. I am looking at a way to automatically take data from one sheet, and based on the "type of data" automatically slot certain things per row into 5 separate tabs using a function.

For example, I want take data classified as "FTL", and within another tab, have the "from", "to", "Box/pallet count", and "cost" be uploaded here for further analysis.

I want it to be able to continuously update any time things are added into the "order Data" tab.

Side thing - I have currencies in USD and CAD, what formula can I use to set a conversion rate and say "if" this is USD, take conversion rate, and convert cost to CAD.

See attached photos for further detail. 

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 30, 2022
    so this can be easily done using the FILTER() function (assuming you have a modern excel). Basic format is =FILTER(dataTable, dataTable[Freight Type]="FTL","none")
    this will pull all the columns but you can select specific columns using CHOOSECOLS like
    =FILTER(CHOOSECOLS(dataTable, 2,3,4,6,12), dataTable[Freight Type]="FTL","none")
    That said I also recommend you consider PivotTable as not only can it pull and filter your data but can also give you totals and subtotals and counts and many other calculations. And while I'm at it I could also recommend power query.
    But the EASIEST of all is just define that data table as a TABLE (see home -> Format as Table) and then use the quick filters on the header column. Technically you can just go to Data tab and click Filter also.
    As usual there are lots of ways to skin the cat in excel and depends more on what you need to do next as to which approach is best/most efficient/more flexible for the future.

Resources