Dynamic, Horizontal Named Ranges for Use in Dependent Drop-Down Lists

[Liam_Reed]

Hi folks,

I've been having some trouble with using a dynamic, horizontal named range in a dependent drop-down list. My situation is the following: I am doing up a budget for myself and listing the name and location of each merchant. Some merchants are visited at multiple locations (e.g. McDonald's). I have been able to set up a drop-down to select from a list of merchants that I have previously visited. However, I have not been able to set up a drop-down to select the locations that I have previously entered for those merchants. The following table is an example of what my work looks like.

Merchant	Location	Withdrawal
Merchant A	Location A1	$5
Merchant A	Location A2	$10
Merchant B	Location B1	$20
Merchant A	Location A1	$15
Merchant B	Location B2	$10
Merchant C	Location C1	$5
Merchant A	Location A2	$20

I have text tables on a separate worksheet listing the unique and distinct merchants, and their associated unique and distinct locations as dynamic, horizontal named ranges so that they can accommodate new locations automatically. In the above table, I use an in-cell drop-down in the Merchant column to select between Merchants A, B, or C. Once I have selected a merchant, the associated dependent drop-down in the Location column allows me to select from any of the locations for that merchant. E.g., If I select Merchant A from the drop-down to enter a transaction for, I can then use a drop-down in the Location column to select from Locations A1, A2, or A3. My problem is the second part of that process; I just can't get the horizontal named range to display as a drop-down. Currently, based on this example, I am using the formula: =OFFSET(first_cell_of_named_range, 0, 0, 1, COUNTA(row_number_of_named_range:row_number_of_named_range)) I've tried with and without absolute references, too.

Would anyone be able to provide any help? It's really been bugging me why it's not working, I've been trying for ages. I'm happy to send screenshots of my actual work if this example is too confusing. Thanks in advance.

Kind regards,

Liam Reed

[#2]

Perhaps this gives you an idea for an alternative approach: https://jkp-ads.com/Download.asp#DependentValidation

[#3 · Accepted solution]

Thanks for the response. Thankfully, I have figured it out with - I kid you not - the most simple solution in the world. Since my multiple locations for a single merchant were being returned horizontally by a spill formula, all I had to do to refer to that spill formula as a named range as it kept expanding was add a '#' to the end of it. Can't believe it took me one character to solve the problem. Thanks again anyway.