Check box hero needed please.

[Guest]

Hello Boffins, I am trying to put a long list of items in one column on an
excel sheet - Lets say 100 items on 100 rows in 1 column.
I would like to give each one a check box and can do using insert activex
control.
So if i now check 30 of those boxes i would like those 30 items to appear on
sheet 2 as a condensed list. I hope this is possible?

Please help me.
Kev

 

[Bob Phillips]

Use a secondary column with a font of Marlettt. Insert an a to check the
item.

Then select the full range of target cells and in the formula bar enter

=IF(ISERROR(SMALL(IF(Sheet1!B1:B20="a",ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!B1:B20="a",ROW($A1:$A20),""),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bex.kev]

Use a secondary column with a font of Marlettt. Insert an a to check the
item.

Then select the full range of target cells and in the formula bar enter

=IF(ISERROR(SMALL(IF(Sheet1!B1:B20="a",ROW($A1:$A20),""),ROW($A1:$A20))),""­,
INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!B1:B20="a",ROW($A1:$A20),""),ROW($A­1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

Thankyou for your efforts Bob, but when i follow your instructions
exactly and do cntrl shift enter i get a big error message.
Thanks for trying though.
Kev

 

[Bob Phillips]

Did you select all the cells and put the formula in the formula bar? It
becomes a block formula then.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Use a secondary column with a font of Marlettt. Insert an a to check the
item.

Then select the full range of target cells and in the formula bar enter

=IF(ISERROR(SMALL(IF(Sheet1!B1:B20="a",ROW($A1:$A20),""),ROW($A1:$A20))),""­,
INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!B1:B20="a",ROW($A1:$A20),""),ROW($A­1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)







- Show quoted text -

Thankyou for your efforts Bob, but when i follow your instructions
exactly and do cntrl shift enter i get a big error message.
Thanks for trying though.
Kev