 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006SMALL(IF( across multiple worksheets
Hi I been poundering this for a couple weeks so far. I would like to get this formula to across multiple worksheets. =INDEX(Sheet2!$A$2:$A$9,SMALL(IF(Sheet2!$B$2:$B$9=C2,ROW(Sheet2!$B$2:$B$9)-ROW(A$2)+1),COUNTIF(C$2:C2,C2))) So I tried to modify into this: =INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"),ROW(INDIRECT("A2:A9"))-2,0,1)),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2))) But no luck. Let me know if its possible. > Hi > [quoted text clipped - 10 lines] > > Let me know if its possible. Any help ? > Hi > [quoted text clipped - 10 lines] > > Let me know if its possible. Bump! > Any help ? > [quoted text clipped - 12 lines] > > > > Let me know if its possible. Bump! > Bump! > [quoted text clipped - 14 lines] > > > > > > Let me know if its possible. Fin Fang Foom wrote... ... >So I tried to modify into this: > [quoted text clipped - 3 lines] > >But no luck. ... OFFSET is the problem. It won't accept as 1st argument an array of range references in different worksheets. If you want to convert a 3D block of cells into a 2D array, you have to use INDIRECT. There is no alternative short of using add-ins. For example, T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIRECT("A1:A"&(10*COUNTA(WSLST))))-1) /10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(WSLST))))-1,10)))) converts A1:A10 from the worksheets listed in WSLST into a 2D range. Thank You for replying Harlan Grove. Forgive me for I dont understand what you are saying. How would I incorporate what you are saying? Should I change the formula into this? =INDEX(T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIRECT("A1:A"&(10*COUNTA(WSLST))))-1) /10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(WSLST))))-1,10)))),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2))) > Fin Fang Foom wrote... > ... [quoted text clipped - 18 lines] > > converts A1:A10 from the worksheets listed in WSLST into a 2D range. I tried adopting Harlan's approach for your solution, but came across a couple of issues... 1) Excel will not accept the formula unless defined names are used. 2) It returns #NUM! in some instances. Nevertheless, try it and see if it works for you. Assuming that A2:B9 on each sheet contains your data, list the sheet names in a range of cells and name this range WSLST. Then define the following... Array1: =1+INT((ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))-1)/8) Array2: =2+MOD(ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))-1,8) Array3: =ROW(INDIRECT("1:"&(8*COUNTA(WSLST)))) Lastly, try... =INDEX(T(INDIRECT("'"&INDEX(WSLST,Array1)&"'!A"&(Array2))),SMALL(IF(N(IND IRECT("'"&INDEX(WSLST,Array1)&"'!B"&(Array2)))=C2,Array3),COUNTIF($C$2:C2 ,C2))) ...confirmed with CONTROL+SHIFT+ENTER. Does this work for you? > Thank You for replying Harlan Grove. Forgive me for I dont understand > what you are saying. How would I incorporate what you are saying? [quoted text clipped - 32 lines] > > > > converts A1:A10 from the worksheets listed in WSLST into a 2D range. Hi Domenic, I implemented your solution and it seems to only retrieve values in sheet2. It will not retrieve values from sheet3. I dont know what I'm missing. > I tried adopting Harlan's approach for your solution, but came across a > couple of issues... [quoted text clipped - 63 lines] > > > > > > converts A1:A10 from the worksheets listed in WSLST into a 2D range. Domenic wrote... >I tried adopting Harlan's approach for your solution, but came across a >couple of issues... > >1) Excel will not accept the formula unless defined names are used. Perhaps this is version-dependent, but under Excel 2003 SP1 with the following in Sheet1!A2:A9, {"this";"is";"a";"test";"now";"is";"the";"time"}, and the following in Sheet2!A2:A9, {"the";"quick";"brown";"fox";"jumped";"over";"the";"lazy"}, I select Sheet3!A1:A16 and enter the array formula =T(INDIRECT("'"&INDEX({"Sheet1";"Sheet2"},1+INT((ROW(INDIRECT("A1:A"& (8*COUNTA({"Sheet1";"Sheet2"}))))-1)/8))&"'!A"&(2+MOD(ROW(INDIRECT("A1:A" &(8*COUNTA({"Sheet1";"Sheet2"}))))-1,8)))) and it returns {"this";"is";"a";"test";"now";"is";"the";"time";"the";"quick";"brown";"fox"; "jumped";"over";"the";"lazy"} as expected. What was your exact formula that appeared to require defined names? Do you mean fitting this into the OP's original formula requires defined names in order to avoid the 7 nested function call limit? That's likely. >2) It returns #NUM! in some instances. Such as? >Nevertheless, try it and see if it works for you. Assuming that A2:B9 >on each sheet contains your data, list the sheet names in a range of [quoted text clipped - 8 lines] >Array3: >=ROW(INDIRECT("1:"&(8*COUNTA(WSLST)))) If it were me, I'd define the last of these first then use it in the definition of the other two. I'd probably also make each of the OP's multiple worksheet blocks a defined name on it's own. So something like WSLST: a single column, multiple row range containing worksheet names N: the number of rows in the common ranges in each of the worksheets in WSLST S: =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1 Col_A: =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N)))) Col_B: =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N)))) then make the array formula =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) Hi Harlan, I implemented a your solutions and I'm getting a #REF! error. Maybe this might be the problem. When you say > N: the number of rows in the common ranges in each of the worksheets > in WSLST I defined it the number 8 in my named ranges. Or else I dont know what I'm missing. > Domenic wrote... > >I tried adopting Harlan's approach for your solution, but came across a [quoted text clipped - 60 lines] > > =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) Fin Fang Foom wrote... >I implemented a your solutions and I'm getting a #REF! error. Maybe >this might be the problem. When you say [quoted text clipped - 4 lines] >I defined it the number 8 in my named ranges. Or else I dont know what >I'm missing. Yes, N should refer to 8. That can be checked: the formula =N should return 8. Did you name your list of worksheet names WSLST? If so, what are your actual worksheet names? I'am running Excel 2003. Here how I set it up. Col_A =T(INDIRECT(""&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N)))) Col_B =T(INDIRECT(""&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N)))) N =8 S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1 WSLST =Sheet1!$A$2:$A$3 and using this formula: =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) Am I missing something? > Domenic wrote... > >I tried adopting Harlan's approach for your solution, but came across a [quoted text clipped - 60 lines] > > =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) Fin Fang Foom wrote... ... >Here how I set it up. > >Col_A =T(INDIRECT(""&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N)))) >Col_B =T(INDIRECT(""&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N)))) ... These are problems. The first literal strings should be " ' " (without the spaces around the single quote/apostrophe) rather than " ". This is a purely protective measure. If your worksheet names don't contain spaces or hyphens, you don't need to put single quotes around them, but it doesn't hurt when they're not needed, and it prevents errors when they are needed. Hi Harlan, I maded the modifications to this: Col_A =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N)))) Col_B =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N)))) N =8 S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1 WSLST =Sheet1!$A$2:$A$3 Now it gining me a #NUM! error. Are the above name ranges are correct? > Fin Fang Foom wrote... > ... [quoted text clipped - 10 lines] > it doesn't hurt when they're not needed, and it prevents errors when > they are needed. Fin Fang Foom wrote... >I maded the modifications to this: > >Col_A =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N)))) >Col_B =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N)))) ... >Now it gining me a #NUM! error. > >Are the above name ranges are correct? Above definitions are correct possibly except for Col_B. Do your column B ranges contain text or numbers? If numbers, you need to change the T(..) call to an N(..) call. However, there's a problem. The Col_A and Col_B defined name formulas work when entered directly into multiple cell ranges, but not as terms in longer formulas. Yet another defined name is needed. XWSLST: =T(OFFSET(WSLST,INT(S/N),0,1,1)) then change the defintions of Col_A and Col_B to Col_A: =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N)))) Col_B: =T(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) if text =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) if numeric The array formula =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) would still return #NUM! when there are no instances of the C2 value in Col_B. If you want to trap such errors, try something like =IF(OR(Col_B=C2),INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1), "no matches") Harlan, this works beautifully! Thanks very much! Cheers! > Fin Fang Foom wrote... > >I maded the modifications to this: [quoted text clipped - 35 lines] > =IF(OR(Col_B=C2),INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1), > "no matches") Harlan, If I follow the instructions for Part 1 of your post, I have no problem. It works beautifully. I can select Sheet3!A1:A16, enter the array formula, and it returns the correct values. It's when I try to fit it into the OP's original formula that defined names need to be used and that the formula doesn't seem to work. Here's the situation as I understand it... Sheet1!A2:A9 contains: {"A";"B";"C";"D";"E";"F";"G";"H"} Sheet1!B2:B9 contains: {10;12;11;18;12;20;26;28} Sheet2!A2:A9 contains: {"I";"J";"K";"L";"M";"N";"O";"P"} Sheet2!B2:B9 contains: {20;10;18;20;12;14;14;16} Sheet3!C2:C17 contains: {10;10;11;12;12;12;14;14;16;18;18;20;20;20;26;28} Defined names: N: 8 S: =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1 Col_A: =T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N)))) Col_B: =N(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N)))) Lastly, the following formula is entered in Sheet3!D2, and copied down: =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) ...which returns... A A C B E B #NUM! #NUM! #NUM! D D F F #NUM! G H As you can see, the formula doesn't seem to work. It doesn't return the corresponding values in Column A of Sheet1 and Sheet2 for Column C in Sheet3. At least not on my version of Excel (Macintosh Excel v.X). Now, for the $64,000 question. :) Does this work on your system? By the way, I like how you've defined the formulas. It allows the final formula to look a lot nicer... > Domenic wrote... > >I tried adopting Harlan's approach for your solution, but came across a [quoted text clipped - 60 lines] > > =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) Sorry Harlan, I just noticed your recent post where I think you've addressed the problem. I'll be taking a closer look at it... Thanks! > Harlan, > [quoted text clipped - 74 lines] > By the way, I like how you've defined the formulas. It allows the final > formula to look a lot nicer... Hi Harkan, It looks like it's working I need to test it a couple of times just to make sure but it looks good. The solution you provided is very handy when doing a lookup across worksheets that accounts for duplicates. This what I have: Col_A =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N)))) Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) N =8 S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1 WSLST =Sheet1!$A$2:$A$3 XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1)) and using this formula =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) an-array Ctrl,Shift,Enter Harlan when you get a chance can you explain how all these formula work? > Sorry Harlan, I just noticed your recent post where I think you've > addressed the problem. I'll be taking a closer look at it... [quoted text clipped - 79 lines] > > By the way, I like how you've defined the formulas. It allows the final > > formula to look a lot nicer... Hi Harlan, It looks like it's working I need to test it a couple of times just to make sure but it looks good. The solution you provided is very handy when doing a lookup across worksheets that accounts for duplicates. This what I have: Col_A =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N)))) Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) N =8 S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1 WSLST =Sheet1!$A$2:$A$3 XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1)) and using this formula =INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1) an-array Ctrl,Shift,Enter Harlan when you get a chance can you explain how all these formula work? > Sorry Harlan, I just noticed your recent post where I think you've > addressed the problem. I'll be taking a closer look at it... [quoted text clipped - 79 lines] > > By the way, I like how you've defined the formulas. It allows the final > > formula to look a lot nicer... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.