Hi, I am currently calling several named ranges for the use of a function, i.e. "a,b,c,d,..." as below, I was just wondering if i could somehow categorize these under one word to save me having to type it all out each time.
Thanks
INDEX((a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,s,t,u,v,w,x,y,z,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,al,am,an,ao,ap,aq,ar,as,at,au,av,aw,ax,ay,az,ba,bb,bc,bd,be,bf,bg,bh,bi,bj),,0)
UPDATE**
I have put "a,b,c,d,e,f,g,h,..." in a separate cell and am trying to call it by simply referencing that cell. It doesn't seem to be working still, I have tried using the INDIRECT function also, I was wondering if anyone could think of a solution. Thanks
What are you actually trying to do?
Entia non sunt multiplicanda sine necessitate
I'm using an index match as an alternative to vlookup, to search through multiple cell references ("a,b,c...") for a match
You could use a named formula; myArray Refers to: =CHOOSE({1,2,3,4,5,6}, a,b,d,e,f,g)
would i do that with a vba module?
Name Manager > New > Name: myArray Refers to: =CHOOSE({1,2,3,4,5,6}, a,b,d,e,f,g)
a A D C2: =INDEX(myArray, 3) b B d D e E f F g G
I've tried implementing this into my formula, as below but it doesn't seem to be working
ArrayOne refers to:=IF(D25="",IF(ISNA(INDEX(ArrayOne,MATCH(C25,ArrayTwo,0),0)),INDEX(ArrayOne,MATCH(C25,ArrayTwo,0),1),INDEX(ArrayOne,MATCH(C25,ArrayTwo,0),0)),IF(ISNA(INDEX(ArrayOne,MATCH(D25,ArrayTwo,0),0)),INDEX(ArrayOne,MATCH(D25,ArrayTwo,0),1),INDEX(ArrayOne,MATCH(D25,ArrayTwo,0),0)))
Where all letters are named ranges,=CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59},a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,s,t,u,v,w,x,y,z,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,al,am,an,ao,ap,aq,ar,as,at,au,av,aw,ax,ay,az,ba,bb,bc,bd,be,bf,bg,bh,bi,bj)
ArrayTwo refers to:
where ***=60 named ranges=CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60},***************************************)
What's the simplest example that doesn't work? What happens when you watch the formula evaluate?
I have attached a test file showing what i mean. The reason for the complicated formula is because it is searching the 'arrays' based on what is in the dropdowns
I would probably do something like this if I understand correctly...
HTH
Regards, Jeff
I would do that, but the actual document is already set up with about 60 different sets of data to search through, and even if i wanted to spend all that time to move them into one set... i cannot because there is some VBA code that is being used to add extra bits of data to each set (on users request)
I will leave you in Jeff's good hands.
Ok, even if you can't more the data around, does this give you an idea about how to name your ranges and extract the result you desire?
not really to be honest. With the original spreadsheet, i saved the two arrays, of arrays as ArrayOne and ArrayTwo, I was just wondering how i could reference those in that formula, as it wasn't working
If you look at the sample I posted, it does quite the same thing, just not as long of a formula which you have now.
As shg has already pointed out...
What happens when you watch the formula evaluate?
Sorry to sound like an idiot, but what do you mean by that?
I'm guessing you are referring to the Evaluate part?
Click on the cell with the formula >> On the ribbon >> Formulas >> Formula Auditing >> Evaluate Formula
I've just done this but i literally have no idea what i'm looking for, i don't suppose you could go on my original spreadsheet and without changing any layouts see if you can make sense of my formula, sorry
Sorry, I can't make sense of a formula that does not work and it longer than it has to be.
Can I ask, what I posted in post #11, does it return what you desire? If it works, why do we need something longer and unwieldly.
Last edited by jeffreybrown; 09-16-2017 at 06:22 PM.
your one doesn't work, the reason for the extra length of my formula is that it checks both cells for a match, if the second one is empty. also it seems the named ranges are wrong on yours(the drop downs are messed up). Anyway that doesn't matter... all i wanted to know is if i could group a range of named ranges together as another named range and then reference that within a formula.
Can you explain what two cells are being matched? I see in D25, "NINE", but other than match C25 which contains the named range, is there another cell to match.Originally Posted by georgedixon
What do you mean by...if the second one is empty
It is meant check C25, if D25 is empty (the reason for this, is that on my main sheet there is a 3 tier dropdown menu, but some choices only use two tiers)... however like I say it was more about how i can group a bunch of named ranges together under one reference
Sorry, I can't seem to get you what you desire so I will have to back out.
You might try putting all the named range "NAMES" in tables on a separate sheet. E.g.:
Named Ranges:
NameBox =Sheet1!$D$3
Option1 ='option data'!$A$3:$A$5
Option2 ='option data'!$B$3:$B$5
Option3 ='option data'!$C$3:$C$6
OptionBox =Sheet1!$C$3
OptionHeaders ='option data'!$A$2:$C$2
OptionItems ='option data'!$B$3:$B$11
TableOptions ='option data'!$D$2:$E$4
test1 =Sheet1!$K$5:$M$10
test2 =Sheet1!$O$5:$Q$8
=IFERROR(INDEX(INDIRECT(INDEX(TableOptions,MATCH(OptionBox,INDEX(TableOptions,,1),0),2)),MATCH(NameBox,INDEX(INDIRECT(INDEX(TableOptions,MATCH(OptionBox,INDEX(TableOptions,,1),0),2)),,1),0),3),INDEX(INDIRECT(INDEX(TableOptions,MATCH(OptionBox,INDEX(TableOptions,,1),0),2)),1,3))
Last edited by protonLeah; 09-17-2017 at 12:41 AM.
Ben Van Johnson
Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks