Dynamic dependent list - name change

happy as larry

New Member
Joined
Jan 18, 2018
Messages
8
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
Excel Formula:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(D2," ","_"),"&","and"))
 
Upvote 0
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
Excel Formula:
=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
 
Upvote 0
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.
 
Upvote 0
In that case you can just concatenate the "_Child" onto the end.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,212
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top