Hi All,
I need to create a dynamic drop-down list to work with my report that I am busy creating. An example of this report can be found in this thread:
http://www.excelforum.com/excel-gene...-database.html
I know that I can using the =INDIRECT(C3) formula to dynamically validate, however,
- What if the value in C3 is blank? And I want to make all available options available?
- What if my drop-down options are not one word?
Thanks.
Last edited by SalientAnimal; 10-28-2014 at 08:09 AM.
Maybe:
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
What would the nrC refer to?
A Named Range, nrC. In my testing, I have nrA, nrB and nrC. If I put a or b in the cell, it returns the list in nrA or nrB respectively. If it's blank, it returns the list in nrC.
Regards, TMS
Ok, so I tried this and I get an error message saying "The Source currently evaluates to an error. Do you want to continue?"
Where you have nrC, do I replace this with my named range as an example nrDivision?
This is an example of how I applied your formula =INDIRECT(IF($C3="*","CostCentre","Division"&$C3))
Also tried it as =INDIRECT(IF($C3="","nrC","nr"&$C3))
Both give the same error
Last edited by SalientAnimal; 10-28-2014 at 10:26 AM.
That's OK. Accept it ... in my example, nrA, nrB and nrC were the only acceptable ranges. A and B would be OK, as would a blank cell. Anything else would give that warning."The Source currently evaluates to an error. Do you want to continue?"
You can't have a wild card. That would be like saying whatever you put in evaluates to nrC. But it doesn't work.
You would need:... assuming there are no spaces between the word "Division" and whatever is in cell C3Formula:Please Login or Register to view this content.
Regards, TMS
So I did some thinking about this, and I think I might be approaching the drop-down list completely wrong. I'm not sure why I was thinking of it the way I was, but I do think I was trying to do the wrong thing, and would have explained it completely wrong.
The Validation is not really a validation of a named range, but rather a dependent validation. So depending on what I select in C3, I should only get specific options in that cell. The problem I have though is my source list will look something like the below as this is extracted from my database (As you will see, from manager onwards, there can be multiple possible matches, which is why there are 4 Business Sales Direct Entries as and example. However, I only want to see this option once in my first list, then the list of Cost Centre available, then the managers, etc..:
-- Division -- -- Cost Centre -- -- Manager -- -- Job Description -- -- Employment Type -- -- Gender -- -- Race -- -- Age Group -- -- Education Level -- -- Service Years -- Business Sales Direct 1165 Frank De Afonseca Key Account Manager Permanent Male Asian 18 - 30 Matric Less than 1 Year Business Sales Direct 1165 Frank De Afonseca Key Account Manager Permanent Male Asian 31 - 40 Diploma / Degree Less than 1 Year Business Sales Direct 1165 Candice Bell Specialist Business Solutions Permanent Male African 31 - 40 Matric 1 - 2 Business Sales Direct 1165 Frank De Afonseca Key Account Manager Permanent Male Asian 18 - 30 Matric 1 - 2 Converged Solutions 1176 Rudie Errenrich Project Co-Ordinator Permanent Female White 31 - 40 Matric 3 - 5 Credit Control 1115 Reginald Thantsha Credit Controller Permanent Female African 31 - 40 Matric 5 - 10 Credit Control 1115 Reginald Thantsha Credit Controller Permanent Female Asian 31 - 40 Matric 5 - 10 Cresta 1141 Katarina Coetzer Consultant Retail Permanent Male Coloured 18 - 30 Grade 8 - 11 3 - 5
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks