00:00This is going to absolutely blow your mind. I've got three very, very simple Excel formula to manage
00:05your inventory. Just three simple formula and it's going to look something like this. Check this out.
00:10I'm going to start off with displaying a text-based level of the inventory level, which means that
00:16let's say if your inventory is 500, I'm expected to see the word normal next to it. The formula to
00:21use is equal if with an S open parenthesis if this inventory is less than or equal to say 400,
00:28which is your order now inventory. I'm going to press F4 to make it fix because I'm going to apply
00:32to the rest of the rows, comma. We're going to display the text order now. I'm going to press F4
00:37again. Like I said, I'm going to apply this same formula to the rest of the rows, comma. And the
00:42nested condition would be if K5 is less than or equal to this low level, F4 again, to make it fix.
00:50I'm going to display the text low, F4 again, to make it fix. And the final condition will be if K5
00:57is less than or equal to the normal level. Press F4 again to make it fix, comma. You're going to
01:03display the normal text F4, close parenthesis, and hit enter. As you can see, it's showing normal
01:09because it's above the 500, which is a low threshold itself. And now select this cell and double click
01:16this filler handler like that. And you will apply the same formula to the rest of the row. That concludes
01:22your level. Now we're going to work on the filter. Select your cell D8 here. You're going to go
01:27to data tab on the ribbon here. Data tools, data validation, data validation, a pop-up window
01:34should appear. Make sure you're selected on setting tab and allow. You're going to select
01:38list and the source. You're going to click on this button here and basically select this
01:43normal low order now and hit enter once and twice one more time. And if you do this pulldown,
01:49you can see you do have three entries on your pulldown based on whatever you have selected.
01:53I'm going to select low here to test it out. And the final formula will be using equal filter.
02:01The first argument will be the complete data set on your master database here. I'm going to scroll
02:06all the way back here. And the second argument on your filter is the condition itself. The condition
02:11will be level. The entire column itself is equal to whatever filter that the user has selected like
02:19this. And close parentheses and enter. You can see it will display all the low values. And if you were
02:24to change this to say order now, you will only show order now. And you can also dynamically make these
02:30changes. Like for example, if I put this to say 600, you can see if you were to select low, you can see the
02:38low value changes. If you make it a bit narrow, like say 450 like this, you can see the number of rows
02:46decreases. Likewise, you can also, I'm going to push this up to 500 here. If you select this to order now,
02:52you can see that if you make it a bit wider here, you can see it, the list become narrow as well.
