Excel drop-down lists - conditional?

[crispybits]

Right then peeps

I'm building an accounts spreadsheet for the charity I'm a trustee of, and however much I google I can't find a way of doing this so kinda hoping one of you has a good idea.

One of the columns is a drop-down list containing various kinds of broad expense category - salaries, expenses, venue charges, class materials, etc

Within each of these categories there are sub-categories, so for expenses these might be mileage, parking, train ticket, bus ticket, etc

I can make the first drop-down list limiting entries to certain specific categories easily enough.

But the second drop-down of sub-categories I can't find a way of making it conditional on what the first answer was. So for expenses you'd have the above, but it would be pretty useless to have mileage as a sub-category of venue charges or class materials for example.

Anyone got any bright ideas about how I can do this. The only way I can think of is having cells out to the right of the data cells which aggregate these two cells together, and then go to a vlookup list which gives back valid or invalid combinations, and then use conditional formatting on the rows so that they will stay all red until a valid combination is chosen, but this will lead to the second drop down list being massively long.

Ta

[WilBert]

This might help crispy, been a while since I've done it myself though.
http://www.contextures.com/xlDataVal02.html" onclick="window.open(this.href);return false;

[Surprised]

http://blogs.office.com/b/microsoft-exc ... lists.aspx" onclick="window.open(this.href);return false;

That may be of use.

[unc.si.]

you need 2 ranges to determine the drop down values

how about making the second data range dependent on the value input into the 1st drop down list.

ie, in second data range (ie the range that determines the sub-categories), put something like If(A5="travel","mileage",if(A5="subsistence","hotel",if......))

Bit of a faff and a bit clunky, and you'd have to limit it to say 5 subcategories for each main category (and put nested if statements in each of the 5 cells picked up by the subcategory drop down), but would do the trick if you can't find another way of doing it.

[Mystery]

Pretty much that really.

The first category is bound to a list of primary categories. The sub categories are listed on another sheet under their main categories.

On the input sheet have an array formula which looks across the headings to tell you which column to look in. Then also use an offset formula to look across that many columns to populate a list of possible sub categories which you then bind the subcat column to.

Can build this if you ask nicely!

[crispybits]

Thanks peeps - the links were very helpful

And thanks for the offer Mystery but I'll get it done myself - need to learn it so I can fix it if they break it

[Surprised]

Using data validation with the source as an INDIRECT formula works well

Set up a sheet with the main categories and under that put the sub codes and name the range for each sub category.

Have one simple data validation to pick the main category.
Make a simple vlookup to get the range name of subs for whichever main is picked.
Lets say this is in a2.
In the data validation for the sub code then use =indirect(a2) in the source box.
This will use the defined name for that range.