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