Forum Discussion
Issues with IFs And Formula
What I am trying to do next is combine this in to a Monthly report not every account goes daily so I was putting in a if formula to search for the account name and if it = that then next test is to see if they were early, on time or late. and place a letter in the box for that day.
As you've noted by now, there's no need to send mtarler and me separate replies (unless they're different). This is a public forum.
That said, may I ask if you'd be open to tracking the daily deliveries all on a single sheet, a single data base? Excel works well in analyzing data when the "raw" form is contained in a single Table. It makes the formulas simpler..they still need to recognize a date and a client--easily accomplished by making data a column of its own. We humans find it easier to look at raw data split out--as if it were on a paper ledger sheet (which may well have been the genesis of this system), but Excel, computers in general can do the heavy lifting of recognizing and separating each element (On Time, Late, Early) by client and date.
I am open to a having a all the daily reports on one sheet and the second sheet to show the monthly total.
Chris
Here (attached) is a start, using a single database for all individual days, and a single formula copied across the monthly summary. That formula takes advantage of the FILTER function, which does require a new version of Excel.
I only did the first four days of data, but that should be sufficient to demonstrate the process.
The formula is this:
=FILTER(Table1[Delivery Status],(Table1[Date]=Monthly!F$2)*(Table1[Accounts Name]=Monthly!$D3),"")
And what that does is filter the column containing "Delivery Status" and show the result where
- the date matches the date in row 2, at the top of the column (F$2 accomplishes that; changing to G$2, H$2, etc., as you copy the formula across all of the monthly dates)
- the account matches the account in column D ($D3, which retains the D but increments the row number as it gets copied down, so covering any number of accounts)
This does assume that there's no more than one delivery per account per day.
Here's a good reference for learning the FILTER function. https://www.youtube.com/watch?v=9I9DtFOVPIg
- One last question is that I would need to Self Populate the began and end times depending on what account is listed with out manually putting them in each time. What would be the Formula for this?