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.

Product

(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.

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:

Furniture

Computers

HousesIn B1 create a Data...Validation drop down using

Allow: List

Source: A1:A3Put 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”

Furniturefrom 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.

Thanks heaps, I ended up getting all my drop down boxes sorted correctly. I also worked out how to add the units also =SUM(COUNTA(D12,F12,G12))

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

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.

=IF(A1="Yes",SUM(B1:B3)+100,SUM(B1:B3))

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

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

Furniture

Computers

HousesThen the category under furniture has say

chairs

tables

loungesI 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,

A2,chairs

A3,Loungeetc. 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

You've used the words value taba 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_arraysbased 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

Furniturewhich looks like this:chairs tables loungesIn the column next to that list place the values:

chairs 100 tables 200 lounges 300Select these cells and name the range

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

FurnitureYour dependant drop down in C1 might show

chairsTry this VLOOKUP construction:

=VLOOKUP(C1,INDIRECT(B1&"Value"),2,0)

which, in this example, evaluates to:

=VLOOKUP("chairs",FurnitureValue,2,0)

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_arraychanges 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.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History