Formula

[Jonathan_Pineiro]

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)  

ETC

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 ().

Lets say:

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.

[#2 · Accepted solution]

@Jonathan_Pineiro 

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")

[#3]

Thanks a lot

[#4]

WHAT NUMBER OR LETTER SHOULD I CHANGE SO I CAN REPEAT THE SAME FORMULA FOR THE NEXT ROW LIKE A4;A5;A6 ETC? I TRIED CHANGING. ALSO IF I WANT TO EXPEND SO IT CAN COPY UP TO CELL K, WHEREN SHOULD I PUT IT? I TRIED CHANGING F FOR K ON THE FORMULA BUT THAT DIDNT WORK. IM SORRY I NEVERD USED EXCEL BEFORE SO YOU ARE REALLY HELPING ME A LOT. THANKS IN ADVANCE

[#5]

@Jonathan_Pineiro 

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.