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
Excel drop-down lists - conditional?
- crispybits
- Dumbledore
- Posts: 5499
- Joined: 10 Aug 2010, 09:18
- FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7
- FPL:
- WilBert
- Dumbledore
- Posts: 7767
- Joined: 28 Dec 2009, 21:07
- Location: 2nd in SP4's Ashes comp.
Re: Excel drop-down lists - conditional?
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;
http://www.contextures.com/xlDataVal02.html" onclick="window.open(this.href);return false;
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel drop-down lists - conditional?
http://blogs.office.com/b/microsoft-exc ... lists.aspx" onclick="window.open(this.href);return false;
That may be of use.
That may be of use.
- unc.si.
- FISO Knight
- Posts: 11811
- Joined: 11 Oct 2010, 14:08
- Location: Off to buy Loctite
- FS Record: 'Loser' by Beck
Re: Excel drop-down lists - conditional?
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.
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
- FISO Knight
- Posts: 13816
- Joined: 13 Oct 2005, 18:33
- Location: Just about here
- FS Record: 116th in TFF 05/06
- FPL:
- Contact:
Re: Excel drop-down lists - conditional?
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!
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
- Dumbledore
- Posts: 5499
- Joined: 10 Aug 2010, 09:18
- FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7
- FPL:
Re: Excel drop-down lists - conditional?
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
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
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Excel drop-down lists - conditional?
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.
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.
View Latest: 1 Day View Your posts