Dynamic dependent list - name change

[happy as larry]

Hi all

I am trying to use dynamic but dependent lists, but using named ranges. Obviously the named ranges will not allow spaces or the "&" symbol. However the lookup names in the dropdown list need to be normal text.

EG: Normal text is "Digital Technology R&D" But the linked/dependent named range has to be either "DigitalTechnologyRandD" or "Digital_Technology_RandD".

What I would like to do please is use a formula to look at the true name of "Digital Technology R&D" and substitute that name for the named range, so the indirect lookup works.

I'm happy to re-name the range etc if necessary, but the dependent lookup cell needs to be the original text so users can navigate. IE: They need to select the true named departments.

I am using Office 2021

Many thanks
Mike

 

[Fluff]

I am using Office 2021

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about like

=INDIRECT(SUBSTITUTE(SUBSTITUTE(D2," ","_"),"&","and"))

 

[happy as larry]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about like

=INDIRECT(SUBSTITUTE(SUBSTITUTE(D2," ","_"),"&","and"))

Thank you for your quick response, I will update my account details shortly and also try your solution.

One other addition though please, I have extensive lists and drop downs, and I need 'parent' and 'children' cost centres. On that basis I have 2 similar named ranges, but one with "_Child" added at the end. Therefore, can I use concatenate or similar to add to your formula above? and therefore add the additional text for the lookup to work against the specific named range?

Many thanks
Mike

 

[Fluff]

Therefore, can I use concatenate or similar to add to your formula above?

Yes you can do that, but how would you know if it was a child or parent?

 

[happy as larry]

Yes you can do that, but how would you know if it was a child or parent?

I'm looking at the first list (cell A1), from the 3rd dependent drop down (cell C1), and then adding the narrative "Child" to the lookup. The formula then looks for the table data named "XXX_Child" or "YYY_Child" etc. As I have multiple lists (and therefore named ranges) of data for parent and child.

 

[Fluff]

In that case you can just concatenate the "_Child" onto the end.