Forum Discussion
Named Range Reference
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.
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
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.
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