Thank you so much. Looking for just this functionality. Unfortunatly, I can't download the example spreadsheet though. Google drive says I don't have access. Could you help?
Thanks! When I add another column for "quantity" in column c and manually input a 5 for Bluetooth Headphones and create a subtotal column using =(C5*D5) I get a #VALUE error. How can I fix this? I want to be able to build out how many of each quantity the customer is buying and keep a running total of pricing. Ideally when I do this equation, I get (66*5) = 330.
@@ELKAMELBI I duplicated the same document as shown in the video. The formula shown works but now I want to take that result and multiply it by a quantity. Thank you!
@@BrandonDorame-je5tu I don't see why this is an issue, if you put quantity in column D because column C already contains the unit price, however, if you switch companies the quantity will not change automatically. if you want to have quantities appear automatically, you need to add a quantity column for each company table and then create a function in the main sheet similar to the one that brings in the unit price. hope I understood you question correctly.
Elkamelbi, A great video. Could you provide a formula IF my table (price list) is in another open excel file? I would like to have my different price lists in a separate Excel file from the Invoice file.
@@ELKAMELBI i see, i guess what i am trying to say is what if i wanted to keep each customer on a different sheet tab, what would the formula be then? I guess this could be done in a different video. Also, how can we download the sample excel sheet? Even if I sign in with my Microsoft account it gives me error.
@@jules1599 The link is working fine on my end. I opened it in a private tab without login and it opened the file. did the file open for you? if yes, just go to file menu and then Save As and then click on Excel, if you are still facing issues send me an email and I will send you the file. as for separating the customers, are you talking about the price lists for company1, company2, and company3 in the example? you want to put each one in a separate sheet? if that is the case then as I said just cut them and paste them and everything will continue working as is. if you mean something else, please elaborate 😊.
Let's say I want to insert items to a company list how does that affect the setup? Also do the item list must all be the same? Let's say I don't sell item A to company 3 so I i don't want to put It on the list. Thank you for your video!
The template is designed as a single price list for all of your products, the only added feature is enabling you to set different prices for each targeted customer. you can remove items and others but you will lose the main purpose of the template. I think what you maybe referring to is invoice, with an invoice template we can control what product we make available to what customer and maybe apply a certain discount after certain quantity is ordered and things of that nature. I think it is a great idea for another video, thank you! most likely I am going to work on it, will keep you posted.
Thank you very much for the training. how do I apply this to a sales to a sales worksheet where when you select products in different rows sold to different companies picks the price for the particular customer?
Great video, thank you. What if I add one column for quantity, how do I extract the rows with quantities only for a separate quote sheet by not using drop-down menu? Thank you!
Sorry for the late reply, will make it up to you, i am having a problem understanding what you are after. Please send me an email with your sample file and I will help you, email to: abdallah.elkamel(at)gmail(dot)com.
Thank you so much these valuable video. What if we have so many customer and so many products ,and their different type of discount level. How should be formulated? Basically we would like to apply different dicsount level to diffrent product range at the same time customer has different discount rate.
you can define a table for tiers of discounts and have a table for assigning discounts to individual products or whole categories, also add discount table for customers where you have a customer name (lookup value, dropdown list column), date column and discount column, this way you are not limited to a fixed discount. however, your calculations should take into account the date. another way is to use limited time promotions to certain product categories (product range). so in summary you need to have a product table, categories (product range) table, customer discount table and product discount table. this is a bit of considerable work to get everything sorted out correctly, I can do that for you for a fee, let me know if you are interested by sending an email to abdallah.elkamel(at)gmail(dot)com.
HI, Can you make video? in my case if you have different price lists from suppliers , for same items but with different prices. i want to make one list with the product with the cheapest prices corresponding to the supplier that offers the product for the lowest price. So in one sheet the the cheapest company. Thanks in advance
First of all thank you for the suggestion Toni, this is great example. I will put it in the pipe line, soon I will create a video on how to achieve this. Keep an eye on the channel for the next few weeks.
Wow thank you so much. Great video Here is another one. I fill in the party code and it auto populates all the parties profile and order details how do i link forexample pricelist 1 which is an autofill with the chargeable prices. Secondly can you guide me how can we save an invoice into anautomatically generated summary list on a sperate page. Forexample i enter an invoice here and this enters the invoice detail on another page in the first row. I enter a second invoice and it saves another detail in the next row
sorry took so long to reply, I am working on a small business invoicing and inventory system that I am planning to do a video for, same principle might be useful for cashiering, not sure when i will have it ready though.
Hi this is actuall good value addition to my knowledge experience.thank you very much for that. in addition to this, I am currently working with 45 insurance companies for health care procedure pricing. there are 26000 procedure (procedure codes are unique for all companies) price list from each companies which are different to company to company. I want to produce a master price list, which should be enabled me to ,once I entered a procedure code then it should produce pricing for that procedure for all the companies in my list. can you please help me to create such master price list .
When you name the tables Company1, Company2, and Company3 does that also create a named range? When I try doing this, I will type in the desired table name but it remains Table1 and creates a named range with the name I typed in.
It gets listed as a table under Name Manager, it functions in a similar way to a Named Range, difference Table expands with data you don’t to change range every time you add new data. Long story short, when you format data as table you no longer need to use named range.
In this case, I would put them all in one table and create an invoice that pulls out only the rows that belong to the selected company. Check my video about creating the Smart Small Business System - Creating The Invoice
Hi, Data Secrets. I had sent you a personal email for a favor but never returned any. I want you to assist me with something, please. I have a spreadsheet for my products, those products change costs from the manufacturers on a regular basis and if it happened I will have to change my sales price as well, please how can I make it dynamic. Already I have a separate sheet for all the product prices, all I want is to just be able to return the current changed prices without affecting my previous sales. I want if any of my product prices changed it should only be effective on the current sales, not the formal. Kindly help. Thank you. I wish to hear from you ma.
Thank you for this video. I had one doubt sir? You used 3 customers. I want to add around 300 customer. so that I followed this same procedure or if there is another procedure please let me know sir Thank you sir
That will be impractical, another way could be by applying promotions with valid dates for the promotion. So you would have a price list with standard prices and another table with promotions, for what company and dates that this promotion is valid for. After that, you will then create a price list that applies the promotion based on price list date or month, it depends how you would like it to work, if that is something that works with you, I can help you make it.
@@ELKAMELBI - wouldn't it be more practical to establish 10 or less price structure and then assign one of the 10 structures to each customer? Edit: of course using a separate drop down list - Call them D1,D2,D3,D4 ... etc.
Hi Shiva, sorry took me a bit to reply to your comment. so yeah, name manager doesn't accept spaces so if your company name has spaces, when you name it in the name manager you need to put _ underscore in place of the space and when you use the company name in the vlookup function, run it against SUBSTITUTE function, your function will be something like =VLOOKUP(B5, INDIRECT(SUBSTITUTE(C2," ","_")), 2, FALSE). hope that helps.
Sorry I wasn't available to reply past few days, had to take care of some business. yeah probably i can, drop me an email at abdallah.elkamel@gmail.com, we can discuss the details
![How to keep Inventory using an Excel Spreadsheet - [create your own Template]](http://i.ytimg.com/vi/msQoTuCsKJ4/mqdefault.jpg)
The instructions here helped me build an Estimate calculator with different price lists - thank you so much!
Glad I could help, thanks for the feedback 😊
Thank you so much. Looking for just this functionality. Unfortunatly, I can't download the example spreadsheet though. Google drive says I don't have access. Could you help?
Thank you! Exactly what I needed
Thanks! When I add another column for "quantity" in column c and manually input a 5 for Bluetooth Headphones and create a subtotal column using =(C5*D5) I get a #VALUE error. How can I fix this? I want to be able to build out how many of each quantity the customer is buying and keep a running total of pricing. Ideally when I do this equation, I get (66*5) = 330.
Please help!
Please send me your file by email?
@@ELKAMELBI I duplicated the same document as shown in the video. The formula shown works but now I want to take that result and multiply it by a quantity. Thank you!
@@BrandonDorame-je5tu I don't see why this is an issue, if you put quantity in column D because column C already contains the unit price, however, if you switch companies the quantity will not change automatically. if you want to have quantities appear automatically, you need to add a quantity column for each company table and then create a function in the main sheet similar to the one that brings in the unit price. hope I understood you question correctly.
This is an excellent video - I have been struggling with Excel tables, ranges, and dependent lists. This makes it very clear. Thank you.
Thanj you! Glad you found it useful
Elkamelbi, A great video. Could you provide a formula IF my table (price list) is in another open excel file? I would like to have my different price lists in a separate Excel file from the Invoice file.
Thanks, I am currently busy, but I will try and work on it when I have time, thanks for the suggestion.
Great training video. Thanks
This is so awesome! How would it look like if i had each customer on a separate sheet?
It will act the same because we are referring to the table name not the sheet name.
@@ELKAMELBI i see, i guess what i am trying to say is what if i wanted to keep each customer on a different sheet tab, what would the formula be then? I guess this could be done in a different video. Also, how can we download the sample excel sheet? Even if I sign in with my Microsoft account it gives me error.
@jules1599 I will fix the file link issue and look into what you described about separating customers soon as I can, thanks.
@@jules1599 The link is working fine on my end. I opened it in a private tab without login and it opened the file. did the file open for you? if yes, just go to file menu and then Save As and then click on Excel, if you are still facing issues send me an email and I will send you the file. as for separating the customers, are you talking about the price lists for company1, company2, and company3 in the example? you want to put each one in a separate sheet? if that is the case then as I said just cut them and paste them and everything will continue working as is. if you mean something else, please elaborate 😊.
Thanks for the precisely informative video.I was looking for this particular tips from so long.
Amazing! Glad you got here 😊, thanks for let me know
I am unable to use it in google sheets.i tried named ranges instead of tables,can you guide me to achieving the same in google sheets.
Thanks.
This is cool, thanks! Is this still available for download?
Thanks, yes I just found out the link was an old one. I just fixed it.
@@ELKAMELBI Thanks so much 🥰🥰
@@ImperfectPerfection72 you're welcome 🤗
Let's say I want to insert items to a company list how does that affect the setup? Also do the item list must all be the same? Let's say I don't sell item A to company 3 so I i don't want to put It on the list. Thank you for your video!
The template is designed as a single price list for all of your products, the only added feature is enabling you to set different prices for each targeted customer. you can remove items and others but you will lose the main purpose of the template. I think what you maybe referring to is invoice, with an invoice template we can control what product we make available to what customer and maybe apply a certain discount after certain quantity is ordered and things of that nature. I think it is a great idea for another video, thank you! most likely I am going to work on it, will keep you posted.
@@ELKAMELBI have you done this?
Thank you very much for the training. how do I apply this to a sales to a sales worksheet where when you select products in different rows sold to different companies picks the price for the particular customer?
Not sure I understand your case but feel free to send me email and I will try and help
Great video, thank you. What if I add one column for quantity, how do I extract the rows with quantities only for a separate quote sheet by not using drop-down menu? Thank you!
Sorry for the late reply, will make it up to you, i am having a problem understanding what you are after. Please send me an email with your sample file and I will help you, email to: abdallah.elkamel(at)gmail(dot)com.
thank you very much very helpful
You're most welcome
Thank you so much these valuable video. What if we have so many customer and so many products ,and their different type of discount level. How should be formulated?
Basically we would like to apply different dicsount level to diffrent product range at the same time customer has different discount rate.
you can define a table for tiers of discounts and have a table for assigning discounts to individual products or whole categories, also add discount table for customers where you have a customer name (lookup value, dropdown list column), date column and discount column, this way you are not limited to a fixed discount. however, your calculations should take into account the date. another way is to use limited time promotions to certain product categories (product range). so in summary you need to have a product table, categories (product range) table, customer discount table and product discount table. this is a bit of considerable work to get everything sorted out correctly, I can do that for you for a fee, let me know if you are interested by sending an email to abdallah.elkamel(at)gmail(dot)com.
This is awesome! Is this still available for download?
yes of course, sorry the link is out dated, will update, but I will reply to your email for now, thanks :)
HI, Can you make video? in my case if you have different price lists from suppliers , for same items but with different prices. i want to make one list with the product with the cheapest prices corresponding to the supplier that offers the product for the lowest price. So in one sheet the the cheapest company. Thanks in advance
First of all thank you for the suggestion Toni, this is great example. I will put it in the pipe line, soon I will create a video on how to achieve this. Keep an eye on the channel for the next few weeks.
Hi Toni, your video request is up, check it out 😊
@@ELKAMELBI Thank you very much
You're always welcome, thanks for sharing your idea 👍🏻
I am not able to get results as like in this video. It's getting error at the end
You can send ne your file, i can look at it.
Wow thank you so much.
Great video
Here is another one.
I fill in the party code and it auto populates all the parties profile and order details how do i link forexample pricelist 1 which is an autofill with the chargeable prices.
Secondly can you guide me how can we save an invoice into anautomatically generated summary list on a sperate page.
Forexample i enter an invoice here and this enters the invoice detail on another page in the first row.
I enter a second invoice and it saves another detail in the next row
Mashallah sir,,,,,,,,,,,,,,need videos on power query
Thanks, I should do that one day 😅, thanks for the suggestion.
I can not find data validation anywhere on newest 365. Please help
Oh! Really? It should be under DATA tab. Or just search for it in the search bar at the top.
Hi sir, I like your tutorials. Please teach us here how to have a program cashiering using excel.
sorry took so long to reply, I am working on a small business invoicing and inventory system that I am planning to do a video for, same principle might be useful for cashiering, not sure when i will have it ready though.
Hi
this is actuall good value addition to my knowledge experience.thank you very much for that.
in addition to this, I am currently working with 45 insurance companies for health care procedure pricing.
there are 26000 procedure (procedure codes are unique for all companies) price list from each companies which are different to company to company.
I want to produce a master price list, which should be enabled me to ,once I entered a procedure code then it should produce pricing for that procedure for all the companies in my list.
can you please help me to create such master price list .
thanks for your comment and your interest, send me an email and we can discuss details, you can find it under "About" tab
When you name the tables Company1, Company2, and Company3 does that also create a named range? When I try doing this, I will type in the desired table name but it remains Table1 and creates a named range with the name I typed in.
It gets listed as a table under Name Manager, it functions in a similar way to a Named Range, difference Table expands with data you don’t to change range every time you add new data. Long story short, when you format data as table you no longer need to use named range.
What if i have different products as well as the prices with different companies?
In this case, I would put them all in one table and create an invoice that pulls out only the rows that belong to the selected company. Check my video about creating the Smart Small Business System - Creating The Invoice
Hi, Data Secrets.
I had sent you a personal email for a favor but never returned any. I want you to assist me with something, please.
I have a spreadsheet for my products, those products change costs from the manufacturers on a regular basis and if it happened I will have to change my sales price as well, please how can I make it dynamic.
Already I have a separate sheet for all the product prices, all I want is to just be able to return the current changed prices without affecting my previous sales.
I want if any of my product prices changed it should only be effective on the current sales, not the formal.
Kindly help.
Thank you.
I wish to hear from you ma.
your message went to Spam for some reason, but I have replied to your email, have you received it?
Thank you for this video.
I had one doubt sir?
You used 3 customers. I want to add around 300 customer. so that I followed this same procedure or if there is another procedure please let me know sir
Thank you sir
That will be impractical, another way could be by applying promotions with valid dates for the promotion. So you would have a price list with standard prices and another table with promotions, for what company and dates that this promotion is valid for. After that, you will then create a price list that applies the promotion based on price list date or month, it depends how you would like it to work, if that is something that works with you, I can help you make it.
@@ELKAMELBI - wouldn't it be more practical to establish 10 or less price structure and then assign one of the 10 structures to each customer?
Edit: of course using a separate drop down list - Call them D1,D2,D3,D4 ... etc.
Yes your suggestion is practical especially if you have price structure that is designed based on certain criteria not individual customers.
In company names have space so what should i do for that
Hi Shiva, sorry took me a bit to reply to your comment. so yeah, name manager doesn't accept spaces so if your company name has spaces, when you name it in the name manager you need to put _ underscore in place of the space and when you use the company name in the vlookup function, run it against SUBSTITUTE function, your function will be something like =VLOOKUP(B5, INDIRECT(SUBSTITUTE(C2," ","_")), 2, FALSE). hope that helps.
Bro it not accept _ in between names what should i do thanks for u r reply and sorry for my delay replay
nice 👌
Thanks 🙂
Can you help with invoice database
Sorry I wasn't available to reply past few days, had to take care of some business. yeah probably i can, drop me an email at abdallah.elkamel@gmail.com, we can discuss the details