Hi,
I have a file with a main drop-down list + internal drop-down lists
For any data from the main drop-down list.
I started this with the INDIRECT function
The problem is that in the main drop-down list the text is long
Fishing - The_Profession_Permission_Puture_First_About_Earth_Haretz_Including_Judea_and_Somron
And it does not look good in the table with the bottom line (instead of the space)
There is another solution that will allow me to get an internal drop-down list
But without the main text being displayed with an underline instead of a space ????
Note that this solution may not be the cleanest.
In your main file you can create a table which will have the display names and a reference table which will give your display names a named range like "Fishing" for the range of data you want to appear as the drop down list.
Then in the main drop down you can give the description "Fishing - The Profession Permission Puture First About Earth Haretz Including Judea and Somron". What you need to do is in your reference table you need to say that if you select this caption then that refers to the named range "Fishing". Then in your working sheet in the next column to the drop down selection you do an XLOOKUP/VLOOKUP and lookup what named range the drop down selection is referring to. In the data validation option you give the formula "INDIRECT("xlookup column's cell reference")". You can then hide the lookup column if required.
Hope this solution works.
I would love to see the solution in a sample file,
Is it possible?
You could just add SUBSTITUTE to your INDIRECT instead of replacing the spaces in the actual text - eg:
=INDIRECT(SUBSTITUTE(A2," ","_"))
