| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SMALL(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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
Fin Fang Foom wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
Any help ?
Fin Fang Foom wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
Bump!
Fin Fang Foom wrote: Any help ? Fin Fang Foom wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
Bump!
Fin Fang Foom wrote: Bump! Fin Fang Foom wrote: Any help ? Fin Fang Foom wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
Fin Fang Foom wrote...
.... 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. .... 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(W SLST))))-1,10)))) converts A1:A10 from the worksheets listed in WSLST into a 2D range. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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(INDIR ECT("A1:A"&(10*COUNTA(WSLST))))-1) /10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-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))) Harlan Grove wrote: Fin Fang Foom wrote... ... 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. ... 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(W SLST))))-1,10)))) converts A1:A10 from the worksheets listed in WSLST into a 2D range. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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"&(A rray2))),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? In article .com, "Fin Fang Foom" wrote: 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(INDIR ECT("A1:A"&(10*COUNTA(WSLST) )))-1) /10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10)))),SMALL(IF( N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(IND IRECT("B2:B9"))-2,0,1))=C2,R OW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2))) Harlan Grove wrote: Fin Fang Foom wrote... ... 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. ... 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(W SLST))))-1,10)))) converts A1:A10 from the worksheets listed in WSLST into a 2D range. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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. 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. 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"&(A rray2))),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? In article .com, "Fin Fang Foom" wrote: 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(INDIR ECT("A1:A"&(10*COUNTA(WSLST) )))-1) /10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10)))),SMALL(IF( N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(IND IRECT("B2:B9"))-2,0,1))=C2,R OW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2))) Harlan Grove wrote: Fin Fang Foom wrote... ... 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. ... 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(W SLST))))-1,10)))) converts A1:A10 from the worksheets listed in WSLST into a 2D range. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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((R OW(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";"t he";"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 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)))) 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,C 2))+1) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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. Harlan Grove wrote: 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((R OW(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";"t he";"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 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)))) 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,C 2))+1) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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,C 2))+1) Am I missing something? Harlan Grove wrote: 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((R OW(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";"t he";"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 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)))) 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,C 2))+1) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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 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. 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? |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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? Harlan Grove wrote: 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. |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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,C 2))+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") |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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,C 2))+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... In article .com, "Harlan Grove" wrote: 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((R OW(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";"t he";"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 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)))) 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,C 2))+1) |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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! In article , Domenic wrote: 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,C 2))+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... |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
Harlan, this works beautifully! Thanks very much!
Cheers! In article .com, "Harlan Grove" wrote: 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,C 2))+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") |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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,C 2))+1) an-array Ctrl,Shift,Enter Harlan when you get a chance can you explain how all these formula work? Domenic wrote: 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! In article , Domenic wrote: 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,C 2))+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... |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL(IF( across multiple worksheets
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,C 2))+1) an-array Ctrl,Shift,Enter Harlan when you get a chance can you explain how all these formula work? Domenic wrote: 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! In article , Domenic wrote: 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,C 2))+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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
| Line chart from multiple worksheets | Charts and Charting in Excel | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Update multiple worksheets | Excel Discussion (Misc queries) | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
