First post and first wall i've hit were searching forum/google hasn't helped :/.
To avoid a lot of nasty nested if's I have setup several named ranges and I'm trying to use Index and match to match the relevant named range contined in an array of named ranges (VB can probably do this easier but I'm trying to stay excel functions).
eg
So user choices 2 variables in 2 seperate cells which contain 3 and 4 options each.
calculation lower down grabs these two variables and goes and looks in one named range which contains an array of about 2 dozen named ranges. It returns the correct named range...except its just a string I can't get excel to actually refrence the range instead it just outputs the name (I want it to display the named range as a list).
How do I get excel to use the range rather than just output the name of the range??
Digram sort of
User List A: 1,2,3= user picks 3
User List B: A,B,C = user pick B
formula goes and checks array using index and match
A B C
1 1A 1B 1C
2 2A 2B 2C <<named range names
3 3A 3B 3C
it then returns 3B in the cell, how do I get it to use it as named range returning that range as a list ie below
3B contains
Six
Seven
Eight
the named range is working as I can get it to list okay by doing a basic =3B in Data Validation
sorry if I've repeated myself 3 times above and thanks for any help, also apologises if this obvious I'm pretty new to excel.
Bookmarks