Excel Drop Down Boxes etc

Gigabyte / P35-ds3l
August 19, 2009 at 05:27:09
Specs: Microsoft Windows XP Professional, 2.4 GHz / 3582 MB
Ok i know a lil about excel but no advanced stuff. Been playing with a spreadsheet for work and kinda stuck at the moment. I know how to create a drop down box etc, but I like to know how to create extra drop down boxes based on choices from the first one. For example heres what im trying to do, in a difference scenario.

The Data fields are

1.Product - 2.Category - 3.Warranty? - 4.Units - 5.Value

Under the product tab, I managed to create a drop down list box of a few different items. Lets say it looks up data from for example.

(Furniture, Computers, Houses)

I now have a drop down box to choose from these 3 categories. In the 2nd column Category, I want it to check what product category I chose, then create a dif drop down list box. Eg if i choose furniture in first drop down box, it lists dif types of furniture, if i choose computers, the 2nd drop list box only allows me to choose computer items.

Im having trouble getting it to do that, the 3rd one...lets say warranty, it will give a drop down list of Yes, or No. (easy)

Units tab, i want to auto display how many units in total, Category, and Warranty if value in that area, will count as 2 units so want it to display 2.

Last tab is value, Each product and category has a different price value depending on what picked, I want the tab 'Value' to look up depending on the choices made, and if Warranty tab is set to Yes, add say +100 to the total value and list the total here.

Might be simpler than it sounds, but I cannot seem to get this how I want and as compact as possible. I will be using 6 products in total for this exercise, and each product has approx 2-6 categories to choose from each with a different price value.

Im doing this for work for personal use, if someone could help, would be greatly appreciated. Tried finding the info myself, no such luck.

See More: Excel Drop Down Boxes etc

Report •

August 19, 2009 at 06:20:27
Maybe this will get you started…I don't have time to go through everything you need. Come on back with any questions.

To create dependent drop downs, you need to use named ranges and the INDIRECT() function.

Follow these steps as an example:

Put this list in A1:A3:


In B1 create a Data...Validation drop down using
Allow: List
Source: A1:A3

Put your list of Furniture items in another range and name that range Furniture.

Put your list of Computer items in another range and name that range Computers.

Similar for Houses

In C1 create a Data...Validation drop down.
Allow: List
Source: =INDIRECT(B1)

INDIRECT() uses the text argument as the reference to a range. Therefore when the Data Validation dropdown in C1 “reads” Furniture from B1 (via INDIRECT(B1)), it will use that as the reference to the range that you named Furniture and populate the drop down with the data in that range.

Continue cascading your drop downs using this method.

Report •

August 19, 2009 at 07:02:33
Thanks heaps, I ended up getting all my drop down boxes sorted correctly. I also worked out how to add the units also


And formatted the cells to not to display a 0 if no values entered.

Only thing I'm now stuck on is to add the values in a table and add these up. For example, each category chosen, from the product list, will have a different $ value. I'm not sure how to add this into a list as each category item is different. Could someone explain how? Also if warranty is selected to YES which i done with a drop down list, I want it to check if YES and if it is, to add an extra $ value of say $100 to the category item value. This part wont touch the units. Once I got this I believe i'm finished, thanx heaps so far

Report •

August 19, 2009 at 09:46:02
I don't really understand what you are trying to do with "category item value", but the warranty part sounds pretty simple.

Let's assume the Yes/No choice is in A1 and the values you need to SUM are in B1:B3. This will SUM B1:B3 and add 100 to that SUM when A1 shows Yes.


You're going to have to explain the rest of it again.

Report •

Related Solutions

August 19, 2009 at 22:28:23
Each product in each category, each one has a separate unit value. I got the lookup values sorted for the other half i need, but unable to work this part out.
Lets say from your description, pick from

Then the category under furniture has say

I want a sum value dependant on the product, then category and then display this total dollar value in the value tab. For example if i choose in first drop down box furniture, then in 2nd drop down box in that product list, i choose chairs, this chair i want say $500 value. I want $500 then to auto show in the value tab then of course +100 if warranty shows yes.

I sort of know how to do it, but cant quite get how to do it lol. When i done the drop down lists, pulled the data from approx looks like this. If i miss a column the drop down list box displays spaces which I didnt want or that would have solved my problem.

A1-Furniture, B1-Category, C1.Warranty etc.

For the rest of the data i had for example,

etc. Thats how I done my table to pull the data from. To add another table of dollar value for each one dependant on choices..im unsure how to complete lol. Im guessing i need a vlookup, and IF command structure im not sure, im sure theres an easier way. Can always send you what im trying to do if need be lol. any help is naturally greatly appreciated

Report •

August 20, 2009 at 07:56:13
You've used the words value tab a number of times. I'm not sure what you meant by that so I'll assume you mean a cell.

If that's correct, then VLOOKUP will work, but you have to finesse it a little since you will be using different table_arrays based on which category you have chosen.

Remember that VLOOKUP has the following syntax:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Let's go back to the named ranges you used for the dependant drop down lists.

You have a range named Furniture which looks like this:


In the column next to that list place the values:

chairs     100
tables     200
lounges    300

Select these cells and name the range


Now let's say your first drop down is in B1 and currently shows Furniture

Your dependant drop down in C1 might show chairs

Try this VLOOKUP construction:


which, in this example, evaluates to:


Which should return 100

What you are doing is using the text in B1 (your category) to build the range name within the VLOOKUP function so that the table_array changes depending on which category you have chosen.

To deal with the warranty issues and #N/A errors, you can wrap some IF statements around the VLOOKUP to get it to do what you want.

Report •

Ask Question