Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check box hero needed please.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check box hero needed please.
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:$A2 0),""),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) "Kev" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check box hero needed please.
On Jun 7, 12:39 pm, "Bob Phillips" wrote:
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:$A2 0),""),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) "Kev" wrote in message ... 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check box hero needed please.
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) wrote in message ps.com... On Jun 7, 12:39 pm, "Bob Phillips" wrote: 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:$A2 0),""),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) "Kev" wrote in message ... 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 conditions needed to check and count of items based on that | Excel Worksheet Functions | |||
formula needed to sum check marks | Excel Worksheet Functions | |||
formula needed to sum check marks | Excel Worksheet Functions | |||
formula needed to sum check marks | Excel Worksheet Functions | |||
Comparison / Check Off between 2 excel sheets. HELP NEEDED! | Excel Worksheet Functions |