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