Sep 09 2021 10:17 AM
Sep 09 2021 10:17 AM
Hello Excel Community,
About 8 months ago I created a basic excel spreadsheet to keep up with trailers I work with at work. My job is an administrator for Walmart who tracks Shipped and Hold trailers to our service centers. I completely overhauled the hand and paper process I initially learned and fully integrated my spreadsheets for daily use and my supervisors love it. Now I’m in the process of wanting to modify that code for more simplicity, initially when I designed it months ago I wanted the design to be like this but I couldn’t find the correct function. Here is an example of kind of what my functions are set to do.
i control 5 different batches daily so there are 5 separate worksheets within the one excel document . Each are to count my trailers if (countif) a trailer ships; so my current setting is something similar to this:
A1 B1 C1
So A1 would hold my trailer number ex. Trailer (123456)
B1 would hold the percentage ex. 100SL
C1 calculates all SL trailers in the cell for a sum total at end of each shift of shipped trailers
so currently I’m using (COUNTIF B1 = 100SL) again this is a simple upgrade from the hand and paper version I initially learned yet seeing how a trailer can be shipped (SL) at ANY percent I had to also continue the countif function as (COUNTIF B1 = 100SL + COUNTIF B1 = 90SL + COUNTIF B1 = 80SL and so on) this is ongoing for the entire 30+ cells I have populated to work with this function.
What I want to know is there a function than is more simplistic to this that I can use base off of just the signal of an expression: so for example if a trailer is sealed at 80SL (or whatever percent) how can I have excel continue to count anything ending with “SL” after my numerical value is placed instead of having to continue using a string of nested calculation for different values.
Sep 09 2021 11:46 AM
Is it possible for you to post a sample of your actual spreadsheet, or at the very least a simple mockup that replicates the quandary you're facing? (Not an image, an actual spreadsheet)
In the absence of that, or if you would prefer to work just from suggestions--which is fine--then let me suggest the following possibility:
If I were in your place I'd also consider getting rid of the five separate sheets, putting it all into a single database, using a single column to reflect the batch. And yes, another for date, so that a year's worth (or more) can all be in a single table. You'll be even more impressive to your bosses with how you can tabulate comparisons: quarter to quarter, month to month, week to week. Have you explored the Pivot Table feature? You'll love it.
Sep 09 2021 12:24 PM
I really haven't had much training with the pivot tables yet although I'm in MIS graduate that's nothing I delved into yet. However, I would love to know but I've attached a copy of one of my completed shift excel documents to give you an idea of what I was speaking about. Everything within the document from tab 121, 122, 123, 124, and 125 are also calculated on the last EOS tab (which is end of shift report for data keeping)
Sep 09 2021 01:28 PMSolution
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.
Sep 09 2021 03:01 PM