Hi, my boss ask me to create a an excel to automatically calculate the metal price with different sizes and different type of metals. Let's say first table to input the latest metals price and the sizes are by 1 inch times 1 inch. For example, the 1 inch times 1 inch mild steel price is $5 and the stainless steel is $8. If i have a customer list that using 8 inch times 5 inch of mild steel and using 9 inch times 2 inch of stainless steel, how do i just input the type of metals and the sizes in a new table and it can automatically calculate the target sizes' price by the 1 inch times 1 inch size from the first table.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel for material price
0
Answers
0
Selected Answer
There is a rough design in the attached workbook. Look at the Quotation tab. You can select the material and the thickness in B2:B3, and you can enter different dimensions in B4:B5. This is the formula that calculates the price in B.
=VLOOKUP(B3,Prices,MATCH(B2,Prices[#Headers])-1,FALSE)*B4*B5It does so by referencing the pricelist in the worksheet "Prices". Note that the table in it is also named "Prices", and that Excel creates a named range of the table's name which, therefore, is also "Prices".
Pay attention to the cell formats in Prices!A3:A6 and Quotation!B4:B5. Observe that you enter, for example "2" but you see "2.00 mm" or "2.0 in.". If you enter "2.00 mm" the appearance will be the same but the functionality will be destroyed.
Also pay attention to the data validation lists for Quotation!B2 and B3. B2 has a manullay entered list (Mild Steel,Stainless Steel) and you must pay careful attention to spell the words exactly as they are spelled in the table's captions. B3 has a reference to the table (=INDIRECT("Prices[Thickness]")). That is more convenient because you can change items in the table and find the changes immediately reflected in the dropdown. But if you change the column caption in the table the link will be broken until you reflect the change in the validation specification as well.
Discussion
Thanks for your reply, this is helpful! By the way, I still have some concern for that, because I'm a new for excel.
1. How to add another new material at the Prices sheet? What method are u using?
2. Do you have any tutorial video about my problem? Because I still need to add many materials to the table, and I got no idea how to continue with it. It is better if u can teach me how to do. Thank you very much. Cheers!
1. How to add another new material at the Prices sheet? What method are u using?
2. Do you have any tutorial video about my problem? Because I still need to add many materials to the table, and I got no idea how to continue with it. It is better if u can teach me how to do. Thank you very much. Cheers!
gohqd (rep: 2) Jun 3, '21 at 9:18 am
Hi,
I don't answer follow-up questions, especially for answers that haven't qualified for being marked "Selected". But since you are new to all of this: just add another column to the table and add that column's caption to the validation list in Quotation!B2.
There are tutorials for every kind of problem on the web and some of them are on this site. In most cases the problem isn't to find a tutorial but to choose between the different ways in which answers are presented - by video, by explanation or by example. People prefer to learn in different ways and teachers have diverse talents, too. But while this observation is doubtlessly true the point with even greater relevance is where to find the questions that lead to the plentitude of answers. I gave special attention to your post because I sensed that you were struggling with finding a question. You will find your next question by studying the answer you got. The question how to add a column was a good one but there are several more hidden in my answer and you must grow beyond a single thread, even beyond a single source, to ask them all.
I don't answer follow-up questions, especially for answers that haven't qualified for being marked "Selected". But since you are new to all of this: just add another column to the table and add that column's caption to the validation list in Quotation!B2.
There are tutorials for every kind of problem on the web and some of them are on this site. In most cases the problem isn't to find a tutorial but to choose between the different ways in which answers are presented - by video, by explanation or by example. People prefer to learn in different ways and teachers have diverse talents, too. But while this observation is doubtlessly true the point with even greater relevance is where to find the questions that lead to the plentitude of answers. I gave special attention to your post because I sensed that you were struggling with finding a question. You will find your next question by studying the answer you got. The question how to add a column was a good one but there are several more hidden in my answer and you must grow beyond a single thread, even beyond a single source, to ask them all.
Variatus (rep: 4889) Jun 3, '21 at 10:00 am