Named Range Reference

[Liam_Reed]

Hi everyone, 

I'm having trouble with named ranges. The scenario is the following:

- I have a primary dropdown list which uses a spill range (vertical) and will expand and contract depending on what the spill range produces.

- I have dependent dropdown lists for each item in the primary dropdown list.

- These dependent dropdown lists also use spill ranges (transposed horizontally) that will also expand and contract.

- The spill ranges are made named ranges, using a # to reference the spill range, for the dependent dropdown list to use.

My problem is that the spill ranges for the dependent dropdown list will move up or down when the primary dropdown list changes, but the named ranges won't. My dependent dropdown list named ranges are on a sheet titled Reference Index in column H, but the row must be relative. The primary dropdown list is on the same sheet in column G. So, the named range formulas are ='Reference Index'!$H51# (for example) so that if the primary dropdown list moves, the row number for the named range should change to reflect the new position of its associated primary dropdown item because it is a relative reference. The row number does indeed change, but not correctly at all. It changes to some random number that, for some reason, seems to follow the cell that I currently have selected when I open the Name Manager. The actual dropdowns themselves return a Data Validation error because apparently "the value must match one of the listed items", but I don't have that restriction applied and there are no listed items anyway because the dropdown isn't working. The entire thing only works when I use absolute references on both the column and row of the named range, but then it doesn't move with the dynamic primary dropdown list.

It's quite a complicated ordeal and I understand if you can't wrap your heads around it, so let me know if you need some screenshots. Any help would be greatly appreciated, thanks.

Kind regards,

Liam

[#2]

@Liam_Reed The attached worksheet contains a simplified example of what I believe you are describing. If not, please clarify your situation by sharing a file (OneDrive or similar) or, at least, with some screenshots that show how you have set-up you schedule and data validation.

[#3]

@Riny_van_Eekelen 

Hi Riny,

Sorry for the slow reply, I've been trying to figure out other ways around my problem. That worksheet you attached is pretty much what I've described. Unfortunately, it hasn't quite enlightened me to anything. I'm still trying to use a formula to reference a horizontal spill range as a named range to use in a dependent drop-down list; I'll use one of my spill ranges which is located in 'Reference Index'!H119 as an example:

The spill range is a list of McDonald's locations I've visited, and I've assigned it the named range name of "McDonald_s". The formula I've used in the 'Refers to' section of the 'Name Manager' box is =INDEX('Reference Index'!$H:$H, MATCH("McDonald's", 'Reference Index'!$G:$G, 0))# where column H is the array to search for the spill range, and column G is the array to search for the row number that matches "McDonald's". When using the indirect formula in the dependent drop-down list, I've made sure to use SUBSTITUTE to replace the "'" with "_". My problem is clearly to do with the formula of the named range; when I create or edit them, I get the message "The source currently evaluates to an error." and the dependent drop-down list doesn't work. When I use the absolute reference ='Reference Index'!$H$119# in the 'Refers to' section, however, it works just fine. (But this is impractical for me because I need the 'Refers to' source to be relative.)

I've assembled a simplified example worksheet for you to have a look at to see what I mean. Look around at the data validations for the Transactions table and the named ranges for each merchant and you should be able to get a good idea. Thanks for your help.

Kind regards,

Liam

[#4]

@Liam_Reed It seems you are trying to set Data Validation options based on the input in the Merchant column of the Transactions table. Don't think that will ever work. And, a Data Validation list needs to refer to a range (directly or indirectly) or a (spillt) array. The type of formula you tried to use simply don't work in this context. At least, I never managed to get it to work that way.

Note that I used a named formula called Sublist to dynamically generate and array for the dependant drop-down list (i.e. Location based on the Merchant).

=INDEX(Options,,MATCH(Tranactions[@Merchant],Options[#Headers],0))

See if this indeed does what you are trying to achieve. 

If a new Merchant and/or Location is added, do that in the Options table. 

[#5]

@Riny_van_Eekelen 

See, this does work, except my range of merchants is dynamic. It will be added to constantly and this can't be done automatically with a table (unless using VBA, which I don't want to do). Currently, my actual worksheet has 182 merchants and there's more I still have to fill in. I don't have to be manually adding table columns for every new merchant and entering every new location that I visit. That's why I use the spill range of merchants and a spill range of their respective locations, because the spill range can always expand. I've attached an edited copy of my original worksheet to show two formulas that I've tried using as the named range source that work by themselves, just not in the named range. I don't see how it shouldn't be possible, as all both these formulas are doing is referencing a spill range, which is exactly what the named range needs.

Liam

[#6]

@Liam_Reed I believe we are going around in circles. You basically want to create an auto-complete function with Data Validation. Enter transactions by Merchant and Location, without any restriction. It would just give you a list of locations you visited before for that Merchant, but you may also ignore it and type anything you want (even none existing or miss-spelled locations). You are, thus, circumventing the whole essence of data validation. 

Why not invest some time to list out all possible locations for all merchants that you might visit in line of your business and use that as the basis for real data validation, and maintain that list/table in case a new merchant pops up.

If this is not a viable option for you, I can't help you any further. Sorry.

[#7]

@Riny_van_Eekelen 

Fortunately, I have managed to solve my problem with a much less complicated method. I figured out that I can take the INDEX and MATCH formula that finds the locations of a merchant, and use it directly in the data validation formula instead of using INDIRECT. Consequently, I have removed the need to have named ranges at all. I've attached a final edit of my example worksheet to show you what I mean. In brief, problem solved. I'm sorry if it has been frustrating, but thank you for your help.

Liam