I'm working on a Product Evaluation Checklist in which each item has a
checkbox (yes) indicating if that item is present. The list is quite
extensive; so I as the each item is checked/ unchecked I want a list
of all unchecked items generated at the bottom of the worksheet (as a
recap).
I have the checkbox linked to a cell that equals TRUE (let's call it
C2). I'm sure there's a better way to pass the value of the checkbox,
if so please let me know.

Worksheet layout:

_____Col A_____Col B____________Col C_____Col D
R1 ITEM ITEM in SPEC FALSE
R2 widget 1 Checkbox YES TRUE
R3 widget 2 Checkbox YES FALSE
R4 widget 3 Checkbox YES TRUE
etc. etc.

I would like the recap to list all items that are out of SPEC, this is
the idea method.
R200 widget 1
R201 widget 3
R202

I have the following array formula in the recap area {=IF($C$2:$C$4=$D
$1,$A$2:$A$4,"")}.
My results using the above example gives me the following results.
R200
R201
R202
However, if I uncheck the first item (Row2) only, I get this.
R200 widget 1
R201 widget 1
R202 widget 1

Please help.

Thanks,
Jin

Assuming you tested your formula as a formula first, try this with OUT the
{ }

range("a2").formulaARRAY="=IF($C$2:$C$4=$D$1,$A$2: $A$4,"")"


--
Don Guillett
SalesAid Software

"jin" wrote in message
ups.com...
I'm working on a Product Evaluation Checklist in which each item has a
checkbox (yes) indicating if that item is present. The list is quite
extensive; so I as the each item is checked/ unchecked I want a list
of all unchecked items generated at the bottom of the worksheet (as a
recap).
I have the checkbox linked to a cell that equals TRUE (let's call it
C2). I'm sure there's a better way to pass the value of the checkbox,
if so please let me know.

Worksheet layout:

_____Col A_____Col B____________Col C_____Col D
R1 ITEM ITEM in SPEC FALSE
R2 widget 1 Checkbox YES TRUE
R3 widget 2 Checkbox YES FALSE
R4 widget 3 Checkbox YES TRUE
etc. etc.

I would like the recap to list all items that are out of SPEC, this is
the idea method.
R200 widget 1
R201 widget 3
R202

I have the following array formula in the recap area {=IF($C$2:$C$4=$D
$1,$A$2:$A$4,"")}.
My results using the above example gives me the following results.
R200
R201
R202
However, if I uncheck the first item (Row2) only, I get this.
R200 widget 1
R201 widget 1
R202 widget 1

Please help.

Thanks,
Jin

Yes I've tried it without the {} and I get a #VALUE! message. I'm a
little confused with your formula, I assume this formula goes into the
recap cell (I mentioned above); if so I get an error message. Please
advice.

Thanks,

J

I'm a little confused with your formula,
I assume this formula goes into the recap cell

It's not a worksheet formula. It's a line of VBA code that will insert the
formula into the cell through a macro.

How many rows of data do you have? Roughly, how many items will meet the
criteria?

Biff

"jin" wrote in message
ups.com...
Yes I've tried it without the {} and I get a #VALUE! message. I'm a
little confused with your formula, I assume this formula goes into the
recap cell (I mentioned above); if so I get an error message. Please
advice.

Thanks,

J

So is the code entered as a Change Event (Private Sub)? I like to
avoid using VBA as the solution but if that's the only way that's
fine. I have approximately 150 rows/ items. The recap area will have
a lot less items listed; maybe 20 or so. The workaround I have is to
have each row in the recap area with this formula, =IF($O6=
$Q6,$A6,""). But I want to avoid having a 150 row recap area with
only 20 items scattered all over the place. I thought using an array
formula would be the best solution in which I could condense the recap
area with "out of spec items" on one single page (or so).
Thanks,
J

Try this:

Assume:

Widgets in the range A2:A10
Checkboxes in the range B2:B10
Linked cells in the range C2:C10

Enter this array** formula and copy down until you get blanks:

=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))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"jin" wrote in message
oups.com...
So is the code entered as a Change Event (Private Sub)? I like to
avoid using VBA as the solution but if that's the only way that's
fine. I have approximately 150 rows/ items. The recap area will have
a lot less items listed; maybe 20 or so. The workaround I have is to
have each row in the recap area with this formula, =IF($O6=
$Q6,$A6,""). But I want to avoid having a 150 row recap area with
only 20 items scattered all over the place. I thought using an array
formula would be the best solution in which I could condense the recap
area with "out of spec items" on one single page (or so).
Thanks,
J