Array Formulas
If I want all 150 items possibly listed in the recap area (will
probably be 20 items only) then I would have to have your formula in
150 lines, right?
Right. The formula needs to be copied to enough cells that will meet the
criteria so all the results that meet that criteria will be returned. If
it's possible that all 150 items will meet the criteria then you need to
copy the formula to 150 cells.
But, you don't need 150 *different* formulas. You just need *1* formula
copied to 150 cells.
Just enter the *1* formula in a cell then you drag copy that formula down to
150 cells. It's the same formula, not 150 different formulas.
I assume the ROWS($1:1) represents each line in the list of 150 items
No, it doesn't. Don't "mess" with that!!!! That is simply a "counter". As
you drag copy down the "counter" will increment like it's supposed to.
Biff
"jin" wrote in message
oups.com...
If I want all 150 items possibly listed in the recap area (will
probably be 20 items only) then I would have to have your formula in
150 lines, right? But each line would have a different ROW, e.g.
In the recap area:
Each formula representing an item in the 150 list above, right?
R200 {=IF(ROWS($1:1)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:1))),"")}
R201 {=IF(ROWS($1:2)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:2))),"")}
R202 {=IF(ROWS($1:3)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$2: A$10,SMALL(IF(C
$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:3))),"")}
ETC
R350 {=IF(ROWS($1:150)<=COUNTIF(C$2:C$10,TRUE),INDEX(A$ 2:A
$10,SMALL(IF(C$2:C$10,ROW(C$2:C$10)-MIN
(ROW(C$2:C$10))+1),ROWS($1:150))),"")}
I assume the ROWS($1:1) represents each line in the list of 150 items,
so if that's the case won't I need to have a different formula for
each item? Since you didn't have a $ in the second number of
ROWS($1:1) I would have 150 different formulas as I copy down. Maybe
I'm just confused.
Thanks for your help.
J
On May 1, 5:48 pm, "T. Valko" wrote:
Is there a way to copy the same array formula to 20 lines
instead of 150 different array formulas entered in 150 lines?
Why do you think you have to copy 150 different formulas in 150 different
lines?
I'm not sure what you mean by that.
Biff
"jin" wrote in message
s.com...
Thank you very much for your help. It works great. But I have
another question. Is there a way to copy the same array formula to 20
lines instead of 150 different array formulas entered in 150 lines?
If I have to use 150 different formulas in the recap area then I would
have to print out the additional blank pages. I just want to print
one page of the 20 or so items that are out of spec. I've tried
changing the ROWS($1:150) but that doesn't work. Maybe I'll need to
use VBA for this, what do you think?
Thanks,
J
|