And he can basically go from one end to the store or the other so having that ability we can now organize based on the aisle so if we're starting on an aisle it'll go to the highest or one if we start it out it'll automatically organize based on the aisle numbers that's really smart of course we need to set up our products inside the aisle so we can do that we can simply select on a product like this paper goods and all we need to do is just go ahead and drag those into the aisle that we've set up these aisles are automatically set up inside our admin screen where we can put any type of categories we can have multiple aisles we can have up to 20 different aisles we can set each aisle based on what category so we've got all that so i created a little bit of a solution.
For that and i'm really happy and excited to bring it to you by the way we will be going to every other week with these trainings right i know it's a it's a lot i want to be able to create additional courses and resources and products for you and it's kind of hard to do that creating these really incredible applications so i'm going to be able to create even better applications if i go to every other week so keep that in mind every other week we'll be going to that very very soon and i'll be taking a quick holiday in the summer but i'll try to bring you as much as i can i do appreciate your continued support it means everything to me i just ask a few things all you need to do is subscribe to the channel go ahead and click that button below and don't forget that notification icon bell that'll ensure that you get these training sent to you via email right when.
I launch these episodes every single tuesday this application template is absolutely free all you need to do is click on the link down below if you want to learn how to build create develop and sell your own excel based applications i've got an incredible course called the mentorship in fact this extends of course 132 hour course was originally a 21 month course i've now compressed into just five months called the mentorship express with an even lower price so that way.
You're gonna learn how to create your own automatic and passive income very very quickly once you take this course and i'm going to teach you every step whether you're defining developing deploying your own excel based applications i'm going to show you everything how to do that inside the membership course so i hope you join us there that's the mentorship express a new lower price five months you're going to get of course six hours of training every week so i hope it's really so all right let's get started with this training because it's a really fantastic training we're going to go over an overview of what we have and then we'll get into all the features you're going to learn not just about of course like creating a great shopping template and having multiple lists but how to use excel in ways you've never thought of so.
Your store maybe start out at the higher ones on the left and go down so we can have that ability when we go from right to left right then our store set up automatically when we refresh that i just typed this button it's going to go from 15 all the way down so we can reassess our store based on that so it's fully customizable as how you want to lay out your store we've got an iso aisle name this is customal sometimes you call them columns or if you have another language you might want to rename that how you might not want to call miles or something else columns or whatever you want to.
Call them categories i've got a list of categories here we can assign those to items category you can create as many as you want and then we have a list of aisles here right so once we have aisles we have up to two categories we can put in every single aisle and that's really helpful because some larger ones like let's say frozen foods might be more than one aisle but other ones that are smaller may have two so we can put cosmetics and health and beauty in the same aisle we also have an assigned aisle color now this is kind of helpful once we have a color here once we have an item that's been assigned we want to give it a color like this green color right so once i decide this this particular paper towels item is not not assigned to any category however if i drag it over.
Here it's now going to be assigned to the home items in personal care and you'll see that it's also down here right now paper towels is going to be down there and you'll see now just refresh it and we see now that we have paper towels here inside the list so we also can do that right so as we add items to that they automatically will appear below that okay so it's very very simple we know what items have already been and those items in green have all already been added so all we need to do is just drag the item in its location and then it will automatically be added to the bottom of the list there it is toilet paper now has been added down here okay.
Great so we understand the basic we just need to drag them green means it's already been assigned white means it has not yet been assigned so we can simply select a category or we can exclude those items that have already been assigned if i decide to exclude it i'm going to check this box only those items that have yet to be assigned to an aisle category would show up here so that's kind of helpful so if you have a lot of items you may not want we have personal care we've got different categories as we change this for example as we change it to let's say cereal and dry food those particulars i don't have any in this category here but we may have some under cosmetics or home items here we go all the way up here there we've got a lot of items so here's the two items that are dairy that are unassigned however if i were to unselect that.
It's going to show all the items so we notice that two are have not been assigned all i need to do is just drag this over to the local person to the local aisle here whichever one dairy and then it's going to automatically appear below that so that is how we're going to do it as we move along so that that way we decided basically what we're doing is we're assigning a column number a row number or an aisle number to that to each one and of course inside the shopping list we have our items here i can select on multiple shopping lists if i create a digital shopping list i can display those shopping lists if i want to create a brand new item i can create a new item if i want to load an existing item i can select on an item if i want to show a specific i can show all items or.
Posts Related:
I can show only a specific like canned goods it's only going to show those items if i select an item
I can add it to the list here if i want to add multiple items i just select it again and that quantity right here is going to increase as i as i keep clicking there it's going to increase that quantity so automatically increase the quantity if i save and sort that list it's automatically going to be sorted based on the aisle if i reverse that i want aisle 15 to show up if i decide i'm going to enter the store on the right side and i'm going to move from right to left i want that aisle here so i know exactly what order to go and i've got a little picture of the item here and the price and the total and i've got a check box so that when i print my list all i need to do is just print that list it's going to automatically print out to your local printer and then we're going to be able to.Check those items off so here i've got my printer here and we can print our list just go ahead and click the print list it's automatically going to print the list that list is going to be updated automatically and it'll print your local printer whatever your default printer is and you'll be able to check off those items as you purchase them in the store so it's going to be kind of helpful you can include or exclude pictures just by clicking this box same thing for here include or exclude the pictures just by moving this so that's going to be really really easy and relatively user friendly i'm going to show you how to all do all of that in this training so we've got the shopping list we'll be able to add new items save new items delete items individual items we're going to of course once we do that we'll give it item name a description.
We'll be able to assign a picture to that if we want to click a picture we'll be able to select an item just clicking on the box here and then that'll do it we'll be able to select on a product item just by selecting here we can browse for a picture of that notice we've got in our grocery items we've got a lot of pictures set up already notice individual names of these particular items here and those are going to be the same names that we're going to associate in our items list now our list of those items i've got located right here and they're going to include an item id which is unique an item name a description a category an aisle if any has been assigned a price and a picture notice those picture names include the extensions and they're exactly the same ones that we're going to use inside that folder you just saw okay great we also have a shopping.
List database these are a list of the shopping lists that we've created list id unique list id the date of the list the the quantity and the total and then of course we have the individual items that go on each individual shopping list that same list id meaning all these items appear on list one each individual item id the item associated remember those each item has its own unique id so i want to have that inside there the name the quantity on that list the price and the existing row of that database we're going to use a formula and that'll tell us the row that's going to help if we make any updates we're going to need to know what row to make the updates or make the.
Delete or whatever and we're going to be able to have that we're also going to be able to remove individual items we don't want a specific item we can just select the delete and that's going to clear out that item automatically just by that so we'll be able to do that i'll show you how to do that it's going to automatically save and update that so i've got a lot to share with you here so we're going to get started right away so the first thing we want to do is want to show how we create this really cool store setup where we can create these automatically these shapes that are going to be based on the store so we have a list of products that we have based on a filter here we can also show all products or all categories if we want it's going to show all those we can see all those that have been signed or not assigned so we can do that or we can show individual categories.
So why don't we get into the macros that are going to do that and we're going to go inside vba it's from the developer visual basic right if you're new to visual basic i've got a great course by daniel strong odd which will show you how to create every type of macro from the beginning if you have no experience at all that's fine that course is an incredible course by my friend daniel strong i'll include that down on the links down below and that's the ultimate vba course all right so let's get into the code here so what i have here is i've got some store macros store macros are going to help us create that store and we've dimensioned some variables here which we'll be going through as we move through the code so the first thing we want to do is list those products.
I'm going to list those products now if we click on the individual store sheet here we have just one macro on any change to d2 we're going to list those products if i move a little fast feel free to slow down the video watch it as many times as you want okay so store list products this is going to change so that means anytime we make a change to d2 if we were to view here we can go ahead and view those headings we see that it is d2 that we're making the changes to so anytime i make change i want to reload this list now while it may look like cells these are actually shapes.
Shapes going to allow us to drag and drop over that so we can do that as well if we drag it to an incorrect spot it's going to let us know of course if we do that so we can refresh the list and it'll know that there's a timer on that so if we do it wrong or incorrectly we can drag it over anything correct we can assign it to mutant fish if we want to if we decide we don't want to put it in that of course we can change it over to any type of category we want so that's relatively we can drag it over here if we have beer and y we can move it all the way over on the side and drag it into here is that left upper left portion of that shape that we want to put inside the individual column once we do drag it over you'll see that it is here it'll appear below.
That particular ir column okay great so that's what we're going to do but first what i want to do is i want to get this list based on this so basically we're going to run an advanced filter based on whatever the user has put in here and we're going to be filtering out our items right all of our items are located in this database so the first thing we want to do is create some criteria some store criteria and it's going to be based on a category now this particular category is criteria based on an advanced filter so notice this says beverage if i were to change that to another one let's say bread it's going to show us all the bread that we have going back.
Into the items we see that that criteria is now linked it's based on the store setup d2
If we have all categories it's going to show empty meaning no criteria otherwise it's going to show whatever the user has selected in d2 in this case it's spread so i'm going to run it that and i want to know if we're going to be showing a sign or not a sign and what does that mean that means i only want to show maybe i want to exclude assigned items notice everything in this category has been already assigned so there's nothing here however if we were to click on canned goods we see that there's two canned goods that have yet to be assigned to a column however.If we were to unselect this we would show all so by selecting this when we go into our items we see that i'll equal have the equals meaning we want to know only those that are contained that have the aisles that are equal to empty right equal to empty because they have yet to be assigned only two of those results have yet to be assigned an aisle that means item id 18 and 81 if we look down 18 we see that it is the item id here 18 is yet to be assigned here so equals is going to do that for.
Us basically equals empty so that means if the store set up b1 equals true what does that mean well if we take a look inside the store set up here we have an option box here and we see if we right click that we click format button we see that it is tied to b1 b1 so if we slide over to the left we see that this is true if i unselect it it's going to go to false if i select again it's going to go to true so if this is true then i only want to show those particular items that have yet to be assigned an io inside the store so that is what our criteria is going to show inside that equals meaning only those blank however if we unselect it's going to show blank so in this case this is our criteria we want to show only those aisles that are blank that have.
Not been assigned and only those with category of can good i want those results to appear here i'm then going to create shapes i'm going to loop through these i'm going to loop through these starting in row 3. all the last one i'm going to create particular shapes based on these and i'm going to create a picture if we do have a picture based on this picture name right here and that's what i'm going to create these shapes right here inside our store so notice there's two shapes with that and what we'll do is we're going to use the sample shape i've got a sample shape right here now if we were to have an incorrect folder maybe we we don't have any items let's go ahead and rename that that means it's going to be incorrect folder right so if i try to refresh that again what we want to do is we want to just show a default icon so that shopping list.
Icon is default meaning that either there are no pictures or maybe the pathway is incorrect and it is this shape that's going to get duplicated so we're going to duplicate this shape we're going to duplicate this shape and we're going to create a text all right so if we go ahead and correct that file path right by selecting it and taking off that one extra s then we refresh it it's going to make sure to add those pictures and all we need to do is just double click on this and add those pictures in great so then what we want to do is list out those so that's the macro we're going to go in right here and it's going to be inside the store macro so store list products when we.
Make a change to d2 that's the macro that's going to run we're going to focus on the sheet store first thing what i want to do is i want to delete any shapes right if i've got a list of shapes i'm going to refresh this list so to do that we can do that specifically based on the name so this is called item group 18 this is called item group here 81. the individual parts are called text 81 meaning 81 meaning the item id and this is called picture item picture 81. so what i want to do is i want to delete every single shape on this sheet that includes the word item right i'm.
Making sure that your other ones don't have that right any other shape that you don't want deleted make sure it doesn't have the word item because we're going to run a loop to do that we're going to dimension this item shape as a shape so we've mentioned the item shape right here up here at the top here store shape and then we have item shapes so the item shape in shapes in this store we're going to loop through every single one using the in string command based on the name of the shape if it contains the text the string item and that's greater than zero i mean it happens at least once.
Then we're going to delete that shape so what this is going to do is simple loop is simply going to loop through every single shape on that sheet any shape that contains the text i item it will be deleted so what that's going to do is delete them all so if i run that macro up until that point we can see that that our items have been deleted here okay we've got some conditional formatting here that will help us okay so and i'll show you about that in a minute so that's going to delete it if we continue on with the macro it's going to loop through all those results that we have and create those two additional items and that's what we're going to show you right now so now we're going to focus shift our focus to the item sheet this is the sheet that we need to run the advanced filter on we're going to go through all of our lists determine the last row of that.
In this case it's 123. we're going to run this criteria based on here the criteria is right here j2 through k3 we're going to have those results come appear here all the way from m through p if there is any we're going to determine the last results row this case it's 4. if the last results row is less than 3 we're going to exit out of the sub that's what we're going to do inside here so first of all we're going to determine the last row of that item data if it's less than 4 that means we have no items at all we can exit the sub i'm going to focus on the star i want to know if we're excluding the i want to update that criteria based on that right so the criteria if b1 is false.
We're excluding the aisle criteria i'm just going to have that criteria on k2 through k3 however if we are including the owl criteria i wanted to include j2 through k3 what does that mean that means we're either going to have here our category only going to work on a category or we're going to include both the store criteria and the category so it just makes it a little easier defining our criteria based on the type of shirt either we want to include i'll or we only want to focus on the category so that's what we're going to have inside b1 so if b1 equals false we're.
Excluding the criteria what do i mean by that just as a refresher inside that if b1 equals false it doesn't matter whether it's been assigned to an ir not we want to show it here regardless however if it is true we only want to show those ones that have been assigned to aisle and that is why we have this criteria we're going to include the aisle if that's selected otherwise we're going to execute it so this advanced filter is going to be either this advanced filter or this advanced filter our original data is from a3 through g in the last row our criteria differentiates between.
K2 to k3 or j2 to k3 our results are going to appear in the same location m2 through p2 regardless that means those results are going to come directly here m2 through p2 we're going to get those results now we're going to determine the once we have those results we're going to determine that last results row based on column m here that's going to give us that last results row once we have it we want to check to see if we do actually have results so if the last results row is less than three we're going to exit this up i'm going to turn off application screen updating defaults that's going to make things a little bit quicker especially when we have a lot of pictures and determine the picture folder now i have a named range called product folder i want to make.
Sure that the named range is going to be based on this cell here notice it's called product folder okay so that product folder so once we assign a variable to that that variable is going to be called picture folder that's the folder that users assign it's going to come in handy a little bit later okay so now we're ready to run our loop we're going to run our loop based on our items all the way from three to the last results row four results row equals three to last results row i want to extract the item id from column m i want to extract the item name from column n and i want to create a picture path based on that folder that we just got and also whatever is located in.
O in case there is a picture this is our full picture path now if it's correct or incorrect we can check for that a little bit later on we're going to switch our focus back to the store that's we're going to focus i want to exit any loops this first of all we're going to exit i'll show you a little more about this but turning this to true is going to exit out any loops and i'll show you why that has to do with our drag and drop so we're going to set b6 to true that'll exit out any loops loops loops are simply a waiting time when i select here a loop is created notice it goes to false and it's waiting for me to drag and drop if i don't drag and drop in a certain amount of time it's going to go back to true but if i do and i put it in somewhere i can put it right there and.
It's going to be there so it's waiting for us to drag and drop it basically okay so also now we're going to focus on the sample product right i want to create this individual product right to do that i'm going to take this shape right here called and the name of it's called sample product sample prod i'm going to duplicate that but i'm going to give it a very specific name that's very specific name is going to be called item text and then the item id because i want it unique okay so to do that we can say the shapes we're duplicating the shape i'm going to give it that unique id with this string item text and the item id okay once we have that we can then work with it so shapes item text item.
Id we're going to set the left position based on d1 we're going to set the top position based on d and the results will plus one what does that mean notice our first row is 4 here right and we're going to move down 4 5 6 and 7 as we go down our results here are going to start in 3. so if i know the results are of 3 i want to place it directly inside row 4. so all i need to do is just add 1 to the results row so that's just what i've done here the results row that's going to be the top position so the top position will go down further and further as we have more items so if.
We select an item let's say we select here we see that there's multiple items so we move down and it's going to be the same exact as the rows i also want to set the width and the height exactly the same as the row so the width the height is going to take on the same height as that result row and that row and the width is also going to take on the same width of that column right i want to put in a text right what is the text going to go there's going to be particular text in the shape and that text is going to take on the item name right here so we're going to set that shape text.
I also want to know if we're going to color it right if we want to decide we're going to color it i want to know the color very specific because if it's been already assigned how do i know if it's been assigned if i take a look here and i see this aisle if i know that p contains a value an aisle has already been assigned to it however it's not but i want to assign it a very specific color and i want to assign it this color if i decide to change that color then i also maybe want to can change it to yellow then maybe when i refresh it i can change it and it'll just be based on that color here so what we can do is assign it that specific color so if p in the result row is not empty right here p in the result row if it's not empty.
It has been assigned a nile i want to give it a very specific color what color do i want to give it i want to give it the color background color located here in i5 and to do that we can say it does not equal empty then the fill of that shape right give it rgb is simply going to be to the interior color of i5 that is going to assign it that shape if we change it back to green oops let me reassign this macro i change the name of that macro so we're going to assign that macro and that's going to be browse for product folder right here so we can browse for that product folder and it'll browse for our grocery items all right so what we can do is change the color here and then change it back to green and it's going to also associate with that all we have to do is just.
Refresh it and it's going to take on that color again okay great so that's how we simply create the text but what about the picture i want to add in the picture add in the item picture if any first thing what i want to do is i certainly want to add this icon so this particular icon is simply a shape with a background fill of particular icon it's gonna be the sample picture now of course if you want all these pictures all the icons everything that created i'll make sure to include all those resources in our patreon platform patreon is a great platform making sure that you get extra trainings each and every week right at least while we have those or every other week when.
We go to every other week and it'll make sure that you get those along with pdf downloads advanced i'll be doing brand new videos in there and a whole lot more so please join us on patreon that's a great way to support this channel just a few dollars a month okay so what i'm going to do is i'm going to duplicate this sample pick and then what i'm going to do is i'm going to fill that picture with the actual picture of the item so that's the first thing we're going to do sample pick duplicate give it a very unique name called item pick and then the item id and then what we're going to do is we're going to focus on that so we're going to give it a left position based on column e we're going to give it a top position also based on column e plus 1. we're going to give it the same height as that as that row we're going to give it the same width as.
That column then what i want to do is i want to make sure that if we have a correct picture we're going to check to make sure that the picture file path is correct and i want to make sure that if it's correct then what we're going to be doing is we're going to fill it with the user picture that picture path that picture path has already been defined all the way up here so we've defined it we're going to check to make sure it's accurate if it is accurate we're going to fill that picture with that path if it's not accurate then like you said before the path wasn't correct it's going to keep it that icon then all i'm going to do is i want to group right when i when i click on this i want to i want both the picture and the item to automatically be selected the best way to do that is to group them together and to group them together we can do that with this line.
Of code here so what we're going to do is we're going to create shapes and arrange and we're going to base it on an array we're going to write that item text this is that first shape that we created and the added picture is the second shape we're going to group them we're going to give it a very specific name called item group along with the item id which will be unique name so that's all we have to do to create that individual list and of course all we have to do is just simply then turn back on application screen updating okay so that's all we have to do to create this list very cool so how do we create this store this store is automatically created dynamically and that means if we were to decide we only want let's say five aisles for some reason in a small store right.
And then we decide we want to refresh it it's only going to do five aisles and i'm going to hide these but these are going to come in handy so i'll show you those are the aisle numbers so i want to make sure to do that as well and i'll show you exactly how we're going to do that so we can update that dynamically so that means that store automatically shapes or created every time we add that so how we're going to do that we're going to do that with the macro i've just tied the macro to this icon just for the fun of it but it also every time we refresh or every time we add it it's also get created so there's a few ways to refresh that store so that's called store refresh that's the macro that we're going to go over now the first thing what i want to do is i'm going to remove all the existing shapes if we take a look at this little divider it's called divider number.
One and this is called aisle number one divider number two aisle number two and so on and so forth so i want to remove all those shapes so anything that contains the word number we're going to loop through all these are store shapes so this is the also a shape variable here so for every single store shape in shapes all the shapes on this sheet we're going to look for a specific shape the shape contains the text the number right we know the name of that shape you can take your word number so if you see both of them let's take a look at this number three here and this is number so both the divider and the aisle contain the text number so that way that one line of code will remove.
All of that okay i'm going to remove all aisle shapes that means the aisles and the dividers because both of them can both of them contain the word number i also want to remove all the products right there's individual products here i want this to be automatically refreshed if we take a look at specific any shape here which is called io product and then 21 21 meaning the item id so each one of these is called i'll product and then the id so the best way to do that i also want to remove anything that contains i'll product the textile product it's going to delete all those so basically this is going to delete all of them right so if i were to run this macro up until this point you see that everything is and we got some hidden uh form we just got some hidden.
Numbers here it's going to help us identify those aisles for our per number basis okay so we've got that we're going to be doing that in fact i also want to clear the contents of two and three so in other words i want to clear this right now and i also want to clear three too i'm going to clear that because that's going to get redone here so we're going to include that as well i like that better i want to those numbers are dynamic i want to fix that so that only those there we go they're all cleared out now those numbers are going to be added in just a moment as we work through the macro now what we need to do is determine left to the right right are we starting at aisle.
15 here are we starting at r1 well this is going to help us if it's left to right we're going to start an aisle one so it's going to go left to right one through three so when we continue on the macro you're going to see io one through three however if we were to change that right to right to left right meaning right to left then we want to make sure that it's automatically going to be completely changed and that's going to be done here so as we move through the macro it's now 15 through through 14 okay so it's just a great way to do that and of course we'll keep.
It on left to right as we move through it so let's continue on with the macro so the first thing we want to do is determine what is it right to left or left to right so inside here if admin d6 is left to right then we're going to set that first aisle to 1 and we're going to set the last aisle to the aisle quantity now where is that aisle quantity coming from that out quantity is coming directly from here this is a named range called aisle quantities the number of aisles you have in your store so that's going to set it up right there okay great so we know that we're going to be starting from left to right our first one on the left is one and our last one is the our quantity okay we're going to step one right i'm moving 1 two three four five right however if it's right to.