Discover the Price List Template Word for Finance that Transforms Your Document Management

Price list template word for Finance

[Music] hello and welcome to my channel my name is abdal camel and today's video is about answering a question from Pim Parma I guess um so um his comment was on an earlier U video that we uploaded to the channel where we um explained how to apply a discount for um a certain price list for different customers so whenever you change the customer a new um discount will be applied to the prices okay so uh his question was what if we uh what if we want to uh give everyone different prices and not discounts so basically what he wants to do is to have one price list but the price switches whenever you choose a different customer okay so this is what we're going to do on this video as per his request and uh I hope that answers your question all right so let's get started so in this sheet we have our price list this is what we're going to print out this is what we're going to give our customers okay so the first thing we need to have is to have um you know a drop- down menu here where we can select our customer okay um and then the other thing on a separate sheet you need to have uh let's say you have three customers okay so for each one of those three customers design your list for that customer okay so and put that price that you want to give them so for company one we have this table for company two we have this one and Company three we have uh this table here okay and basically uh the ultimate goal is to have this you know list here switches between those three lists here whenever you change the customer name okay uh some things uh you need to be familiar with you know to uh fully understand uh what we're going to do here in this video the first thing is you need to uh have an idea uh what we mean by formatting a range as a table the other thing is you need to um kind of have a um an idea on um indirect function and how to do data validation and um I think that's it and if we if we found something else we you'll uh talk about it okay so um let's go to the list first uh the first thing we need to do here is to uh format each one of those lists as a table okay and when you do that you're basically telling uh Excel that this is a table which is is very similar to a database table which has columns and rows or fields and Records okay so and once we design it like that you see that we have something called table tools here okay with a lot of U other functionalities here uh something very important here is the table name uh now you have the option to U call this table a name it's very similar to the named range okay so we're going to name this with the same company name okay so our company name is company one okay so uh for your case you probably need to put the real company name here but make sure you don't have any spaces okay and if you do then you need to handle that in the function later on with vup um and use the substitute function to get rid of that space option okay so uh okay we do the same thing for uh Company 2 we format it as a table and our table has headers here of course and we make sure that we name the table Company 2 Company 2 with no spaces that's important we do the same thing again for company 3 okay and we name it company three so we have now three tables each one of them is named after the company that we're going to give this price to okay all right so uh we have everything prepared here now so the next step we come here okay and we need to design our drop- down menu so we can select the customer okay or the company that we're going to sell our products too okay so on a separate sheet or probably just in the same sheet as somewhere that um doesn't bother you uh just you know put a header here uh says company and put the company names here company one Company 2 and Company three of course you can put it in a separate sheet if you like you know you still can do that all right so um now uh the thing we're going to do we're going to format it as a table okay and our table has Heather here because this is uh the header that we have and uh the table name here uh we can call it companies okay so we're going to call this table companies so okay the next thing now is to uh use data validation so we can um and you go from the data tab to data validation here because what we want to do we we don't want to allow any value we want to allow only a list so it's going to ask you where is the source of your list and this is where we're going to say okay we're going to use the indirect function basically we're going to direct the um data validation to this list here okay we're going to use the indirect function because uh we want to be able in the future to add more companies to the company table okay and we want them to be taken you know automatically in the drop- down list okay so we write indirect and then we put the uh quotation mark and we write the um table name which is companies and uh between square brackets we write the umu column name where that list exists which is company here okay we close the E equitation mark and we close the parentheses here and now when we click okay you can see that we have our list now so we have company one Company 2 company 3 imagine if you want it in the future to add company 4 you just come here at the bottom of the table and write company 4 and hit enter and you can see that the table's been expanded to include company 4 with it the other thing is when you go back to uh the uh company name you can see immediately that company 4 has been added to the list okay and so this is why we use the indirect function okay so now let's remove U company4 because we don't have it all right so uh let's choose company one so when we choose company one what we want um uh to happen here with the price is we want to display the price for company one from this table okay and when we switch Company 2 we want to switch to the other um prize list okay all right okay so how we're going to do that um if these are unique names which they are and should be because uh you're given a price list you wouldn't be uh you shouldn't have duplicates okay so the price we're going to get the price by using in V cup okay so you need to get familiar with v cup if you are not then you can watch my other video on the same channel about how to use VAB correctly okay so we write VAB good practice is always to brush tab so it uh completes the function and open the first parenthesis for you it's going to ask you where the uh lookup value is and we say okay this is our lookup value this is what we want to look for then next thing is going to ask you where do you want to look for it in which array in which table okay we want to search for it in a table called company one right the uh the price list that we have saved in sheet one so we're going to direct uh the function to that table okay but we want it to be linked to this one here okay so what you need to do you need to use the indirect function again so when you use the indirect function just click here okay but make sure you lock the cell because you don't want it to um uh change reference when you uh drag the function down so we do that by pressing F4 and okay so what the indirect function is going to do is going to take the name in C2 and ask Excel uh do you have something called company one and Excel will come back saying yes I do have a table called company one and then you know the indirect function is going to translate that into uh you know the table itself okay so this is uh how we direct the function to the correct um table okay because you want to take the table name from this cell okay and then it's going to ask us okay we got the table in which column uh the data that you want to bring back here uh is located okay so we know that we have two columns only and the price is uh located in column two okay so we write two and the last thing is you need to have an exact match okay because we're talking about text here and uh we don't want to you know to mess with the approximate match all right so and the function is now ready okay it's always going to take the name in this cell okay and try to find you know uh something called company one or company two and brings back the um the correct range okay so once we hit enter you can see that we got our price and it shows 66 if we go here you can see in company one table the first one is actually 66 so we are okay the only thing you need to do now is just uh drag it and you can see we're getting all the prices that we uh that we need for our price list for company one okay and um if we want to show the price for Company 2 it's just uh a click away uh you choose Company 2 and see how the price will automatically change okay uh same goes for company 3 it's going to change and uh company 3 starts with 56 if you go here you can see it's 56 and the last one is 77 so we're doing good so uh this actually works best if you have a lot of you know companies maybe 20 maybe 30 something like that and uh you have a lot of products and um you kind of have you know all your price list set up but when you want to Brint them out you you prefer to print from one price list okay so uh this is how uh it works if you have any question about it please leave a comment and I'll try my best to uh to answer them okay so uh thank you so much for uh watching I hope to see you next time thank you go