I'm struggling to complete this second dependent dropdown which is pulling information from the first dropdown that consists of both multiple words and illegal characters, such as, "&". my first dropdown is in cell A2. How would you go about this? Thanks in advanced!
Since the name of your second dependent dropdown cannot contain those illegal characters or spaces, you need to remove them before applying the second dropdown. What specific illegal characters might the first contain? How many dependent lists will you be needing? There might be a better way to do this. Can you upload an example spreadsheet showing what you have and want to have? (Go Advanced> Manage Attachments)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
You would most likely use substitute to remove text or illegal characters.
Can you provide a sample workbook so we can inspect?
Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back
Please also mark your thread as solved once it has been.
is there a secret to being able to attach an excel file?
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sample attached
AliGW, thanks for the help!
Last edited by thestatechamp; 07-06-2017 at 10:12 AM.
For each Category, create a named range:
by Default "Frame & Mounts" will named as "Frame___Mounts" with underscore replacing "&" and " " (Spaces)
in DV for B2: (Sub-Category)
=LIST
Source: =INDIRECT(SUBSTITUTE(SUBSTITUTE(A2," ","_"),"&","_"))
Last edited by JohnTopley; 07-06-2017 at 11:58 AM.
Not sure if I am doing your instructions incorrectly or not, but when I do what you suggested I get a similar result as before. The sub-category list populates for "Hardware" and "Misc..." but not "Electric Generators" and "Frames & Mounts"Originally Posted by JohnTopley
Have you created named ranges: i only did "Frames and Mounts" in the file I posted.
See attached:
All named ranges (old ones removed)
yes I did let me give it a whirl again
OKay I got it to work for the "Frame & Mounts" thanks for that!!! now the question is how do I edit that formula so it will work for the 'Electric Generators" category?
You don't need to edit formula;: see the last attachment.
Perfect, I didn't notice you posted this before my last response... Will I have to edit the formula at all if I was to add a new category which had a "-" or would the original formula be enough? For example; "Roll-Off"
Last edited by thestatechamp; 07-06-2017 at 12:36 PM.
If you let Excel create the default names then "-" will automatically be replaced by "_".
Click on cell e.g. "Double-Dutch", then "Formulas"==>"Define Name" and should default to "Double_Dutch"
Sorry but I need the sub-categories for "Double-Dutch" to populate. Which when I have tried hasn't happened. Excel wont let me rename the range Double_Dutch
Last edited by thestatechamp; 07-06-2017 at 12:52 PM.
I think I figured out a solution, while it may not be the best.. I entered the following as the source for the data validation:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","_"),"&","_"),"-","_"))
I appreciate your help a lot!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks