Oh, my, Jeremy. You've done a masterful job of taking what was a paper process and converting it to Excel. The problem is that you've done just that: taken what was a paper process and converted it to Excel.
You have done that--and I've seen this happen in other instances too--by imposing that paper process mindset onto Excel, rather than by re-conceptualizing the whole process as a database which can take advantage of Excel's abilities to report on transactions such as movements of trailers.
You've done a really good job, and your formulas that count dispatched trailers are impressive examples of the COUNTIFS function. And how you've incorporated the Page Footer into some of the tabs so as to note such anomalies as "Must Ship" is impressive. But also depressing, in that you literally are picturing this as a page of a report, with a footer that contains--in the case of "Must Ship" at any rate, something that should be its own column--an attribute belonging with a store code on the database.
For your immediate problem, the one you posted, I go back to my recommendation that you separate the SL and other codes from the number that represents a percentage. I've done it for you as a demonstration, on tab 124, and shown two examples of how you could create some cleaner summary data with far simpler formulas. As a general principle, it complicates things unnecessarily to combine two different types of data in a single cell, as you've been doing. A percent PLUS a code for disposition makes it hard to do things with either!
I've added a couple other suggestions in text boxes.
My long term recommendation would be to get yourself a book (there are many: https://smile.amazon.com/s?k=excel+as+database&ref=nb_sb_noss_2 ) on creating and using databases in Excel. You're clearly smart enough to master this, and there's no reason why you need to take a course--many good Excel users are self-taught through reading of books and manuals and "playing" with what they find there in meeting needs at work. That's how I did it, back in 1971 and 1972, before there were PCs and spreadsheets...on an IBM mainframe using a language called APL. Totally self-taught.
Accepted Solutions
Oh, my, Jeremy. You've done a masterful job of taking what was a paper process and converting it to Excel. The problem is that you've done just that: taken what was a paper process and converted it to Excel.
You have done that--and I've seen this happen in other instances too--by imposing that paper process mindset onto Excel, rather than by re-conceptualizing the whole process as a database which can take advantage of Excel's abilities to report on transactions such as movements of trailers.
You've done a really good job, and your formulas that count dispatched trailers are impressive examples of the COUNTIFS function. And how you've incorporated the Page Footer into some of the tabs so as to note such anomalies as "Must Ship" is impressive. But also depressing, in that you literally are picturing this as a page of a report, with a footer that contains--in the case of "Must Ship" at any rate, something that should be its own column--an attribute belonging with a store code on the database.
For your immediate problem, the one you posted, I go back to my recommendation that you separate the SL and other codes from the number that represents a percentage. I've done it for you as a demonstration, on tab 124, and shown two examples of how you could create some cleaner summary data with far simpler formulas. As a general principle, it complicates things unnecessarily to combine two different types of data in a single cell, as you've been doing. A percent PLUS a code for disposition makes it hard to do things with either!
I've added a couple other suggestions in text boxes.
My long term recommendation would be to get yourself a book (there are many: https://smile.amazon.com/s?k=excel+as+database&ref=nb_sb_noss_2 ) on creating and using databases in Excel. You're clearly smart enough to master this, and there's no reason why you need to take a course--many good Excel users are self-taught through reading of books and manuals and "playing" with what they find there in meeting needs at work. That's how I did it, back in 1971 and 1972, before there were PCs and spreadsheets...on an IBM mainframe using a language called APL. Totally self-taught.