Sep 01 2022 11:59 AM
Sep 01 2022 11:59 AM
I work for a Freight Transportation Company. Every day we book loads from different brokers and then we sell it to the company drivers. In order for us to get pay for it, we have to make an invoice once the load has been delivery. For us to know how many loads we are making per week, we made an excel document were we keep tracking of all of the relevant info for each load as detailed below
(A3-DRIVERS NAME; B3-ORIGIN; C3-PU DATE; D3-DESTINATION; E3-DUE DATE; F3-RATE)
(A4-DRIVERS NAME; B4-ORIGIN; C4-PU DATE; D4-DESTINATION; E4-DUE DATE; F4-RATE)
We book around 50-60 load every week, so for me to keep tracking of what loads needs to be billed every day based on E3; E4; E5; ETC (the cells for due date) I'm creating a second sheet that looks exactly as the original one, but the intention of it is to filter all the load delivering TODAY ().
Juan (A3) has a load from Texas (B3) to Oswego (D3) that has an appoiment for delivery on sept 1 (E3). What I want from the second sheet is for it to be able to copy every cell automatically so when I check on it I can see what loads needs to be billed today. I tried the following formula on the second sheet: =IF('sheet1'!E3=TODAY();A3='sheet1'!A3)
That didn't work, and even if it did Ill had to adjust it for every cell I want to copy. Is there an easier way to make this happen? Thanks, and sorry for the long post.
Sep 01 2022 12:09 PMSolution
You could use Sort & Filter > Filter on the Home tab of the ribbon to display filter arrows in the header row. You can then easily filter the data in place.
Alternatively, if you have Microsoft 365 or Office 2021, you can use the FILTER function.
On Sheet2 in cell A2:
=FILTER(Sheet1!A3:F1000, Sheet1!E3:E1000=TODAY(), "No Due Dates Today")
Sep 01 2022 12:34 PM
Sep 01 2022 12:51 PM
Do you have Microsoft 365 or Office 2021? If so, you need to enter the formula in one cell only and Excel will fill as many rows as needed for you. And to extend to column K, use
=FILTER(Sheet1!A3:K1000, Sheet1!E3:E1000=TODAY(), "No Due Dates Today")
But if you have an older version, this formula will not work at all - you should get a #NAME! error.