Complicated Price Table w/ Drop Down Function

**K-Pac** · 06-25-2014, 07:49 PM

Screen Shot 2014-06-25 at 7.45.38 PM.png

Hi all. I am developing a price table for my apparel website and I am at a stump. So each brand name has its own price table showing various prices based on color and quantity. What I want to do is make a drop down menu from the price calculator so that the item chosen on the menu will be the price table evident. I understand that I have to make a price table for each item but I am unsure how to reference it back to the main page. Please see the picture if you do not understand what I am saying.

**newdoverman** · 06-25-2014, 08:00 PM

Pictures don't really help much. Can you upload a workbook with realistic data?

**K-Pac** · 06-26-2014, 09:43 AM

Hi. I have attached my workbook as requested. Please go to the worksheet named "Calculator". In cell A1, you should see a drop down menu for various products. I would like it so that I can choose a product from that list and the table below the drop down menu changes to the table for that product.

For example, if I select Gildan G500 from the drop down menu, I would like the price table below to be Gildan G500 price table listed on a different page.

Thank you.

Company Price Table.xlsx

**newdoverman** · 06-26-2014, 11:22 AM

Using product names that you have in the drop-down listing is a nightmare to work with. The product names are very long and there are far too many of them to be practical in a drop down list. I suggest that you have a series of short drop down lists that lead to the product that you want which would be identified by a product ID to be used as the name for the product.

E.g. Start off with a drop down list that has the categories of Women, Men, Girls, Boys, Baby etc. followed by a drop down for apparel types...shirts, pants, etc. until you get down to manageable sized lists....you are going to have a large number of lists.

To get the data that you want, presented the way that you want, would require using named ranges that contains the details for each product. Using the product names as names for the ranges isn't possible without eliminating a lot of characters in the names that are not acceptable in the name definitions. The use of a product ID would help a lot. Look in the Excel help file under NAMES to see what is and isn't allowed in names.

Another way is to use the listing that you have for the existing drop down list and add to that the all the details for each product. This will probably produce quite a large table especially if you have to account for a multitude of colours the number of which may vary widely depending upon the product. Such a table could then be addressed by lookup formulae to extract the data for the product desired. This will also present problems in getting the format that you want.

**K-Pac** · 06-26-2014, 02:41 PM

Ok so I can cut down the names of the items so that they are appropriate for named ranges. After name ranging each product, what function do I use so that the table underneath changes with the change in product chosen? Essentially, how do I tell excel to change the table with the corresponding product?

**newdoverman** · 06-26-2014, 04:37 PM

Instead of trying to explain this which would take forever, I am attaching your workbook that has an assigned name for the data that you want to retrieve via the drop down list. I entered this name beside the name on the product list so that it could be used in a VLOOKUP. Where you want the data returned is then selected and the formula on the worksheet is entered. You will have to change this to match what you have done with your product names. All data areas for your products will have to be the same dimension and that dimension will be the area where the data is returned by the formula. This is an ARRAY formula entered with Ctrl + Shift+ Enter.

**newdoverman** · 06-26-2014, 04:48 PM

If when creating names for the product data, if you select an area equal in size to the maximum destination area on the Calculation worksheet, even if it is grossly oversized for the product data, the destination area will be correctly filled in without displaying a bunch of error messages for cells that don't have data. All the named areas should be the same dimension as the destination area.

**K-Pac** · 06-27-2014, 09:39 AM

Wow! Thank you so much! I'm going to try to make a nice organized list with subcategories eventually leading to your function. Hopefully it transfers over after I set up the newly organized list with named ranges and data validation.

**newdoverman** · 06-27-2014, 09:59 AM

One thing that I find that helps is to lay out the data on a piece of paper like an organization chart. Once you have the "flow" correct, the rest is relatively simple.