98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Advanced List Management:
Advanced List Management:
Resolved · Urgent Priority · Version 2007
Fri 25 Jan 2013, 16:45Delegate Brett said...
Brett has attended:
Excel Advanced course
Advanced List Management:
I have used the "data validation tab" buttome to select a list refering excel to a nemed range to nmake up the list which is working perfectly fine.
Eg: List example with Range called "Animal":
Dog
Bird
Fish
My question is this...... I want the answer to this square to drive a further list in an alternative square so if "Bird" is selected then the options will be:
Beak
Wing
Feathers
If Fish is selected then:
Gills
Tail
etc
I am trying to make the secondary list specific to the first answer to avoid a very long list covering all options!
Hope this makes sense??
Mon 28 Jan 2013, 10:08
Edited on Mon 28 Jan 2013, 10:11Trainer Gary said...
RE: Advanced List Management:
Hi Brett
Thanks for getting in touch. A very interesting question, and there is a little bit of setup required to get there.
Create your first drop-down list in the usual way. Then create tables for your second-level lists, and apply range names to all of them. Names can be found under the Formulas tab, in the Name Manager.
Then in the validation use an =INDIRECT function that looks at the first drop-down, and uses that value to figure out which range name to use.
I have attached a file to help explain this process. Look in the Data Validation options and range names to find out more. I hope it helps!
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Dependent Drop-Downs.xlsxMon 28 Jan 2013, 10:29Delegate Brett said...
RE: Advanced List Management:
Excellent Gary thanks - will give it a go.
Mon 28 Jan 2013, 19:04Delegate Brett said...
RE: Advanced List Management:
Gary - Struggling slightly due to what I am trying to do and how it differs to the hypothetical example I sent to you.....
Could do with sending you my file (Like you did for me) but dont want to post it on public forum......
Any chance I can email it??
The Fact is that the options on the first menu arent as simple as DOG / BIRD / FISH
They are SKU's and so are a mixture of Text, numbers and spaces so I cant name the range with the same name as the option becuase it wont accept it eg: Water 250ml......... OR CAN I??
Because I dont know how this function works and what it looks for I am struggling to amend things to create a work around....
Please advise - happy to send you my number unless you have it stored on file ??
Wed 30 Jan 2013, 12:02Trainer Gary said...
RE: Advanced List Management:
Hi Brett
Thanks for your reply. You're right, the names won't allow spaces in.
You can probably achieve the right results with something like the CHOOSE function. If you want to send the file over I'll have a look for you. My address is gary@stl-training.co.uk.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 30 Jan 2013, 12:53Delegate Brett said...
RE: Advanced List Management:
Hi Gary,
Did you recieve my email?
Brett
Thu 31 Jan 2013, 09:16Trainer Gary said...
RE: Advanced List Management:
Hi Brett
I searched around and found a very similar issue solved in this site, where you can download a sample file to have a look at. Essentially you build a reasonably complicated table in the background and power the drop-downs with an OFFSET function.
Have a look at the following site:
http://www.contextures.com/xlDataVal13.html
I hope this helps. If you are still having issues please let us know.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 31 Jan 2013, 10:57Delegate Brett said...
RE: Advanced List Management:
Hi Gary,
Thanks for this - I will need to discuss further with you as this is way out of my comfort zone for want of a better word and as the example you have directed me to is so different I do not have the base knowledge to adapt it.... For example I am unsure how relevant layout is or how I need to structure my data to use this function....
Would you better happy to talk me through it on the phone based on my document that I sent across?
Brett
Fri 1 Feb 2013, 12:53Trainer Gary said...
RE: Advanced List Management:
Hi Brett
I've sent you another file which should do what you need.
Let me know if you have any issues with it.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 8 Feb 2013: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Moving or Copying Sheets Between Workbooks in Excel 2010Here's how to move or copy sheets between workbooks in Excel 2010: |
