Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random list place in column order.
Hi Everyone
I have a list, A3:I25 that you can select items. The selection is done with a CheckBox at each items. I would like the items selected to be listed in column order below the list,ex:A30,31,32 etc.Can you help me, I am not very good with functions or macro. Regards John |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random list place in column order.
One play to try .. something along the lines
illustrated in this sample: http://www.savefile.com/files/1333415 Extract checkbox selections into col.xls Assume 6 source items are listed in A3:B5, viz: Item1 Item4 Item2 Item5 Item3 Item6 6 checkboxes are drawn from control toolbox, with corresponding link cells in E3:F5 (these are set in Properties view. In design mode, right-click on checkbox choose Properties) In E10: =OFFSET($A$3,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3)) In F10: =IF(OFFSET($E$3,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3)),ROWS($1:1),"") In G10: =IF(ROWS($1:1)COUNT($F$10:$F$15),"",SMALL($F$10:$ F$15,ROWS($1:1))) Select E10:G10, copy down to G15 Then in A10, copied down to A15: =IF(G10="","",INDEX($E$10:$E$15,G10)) CF is applied to results range A10:A15 using formula is: =A10="", formatted accordingly to mask with white font/borderless -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote in message ... Hi Everyone I have a list, A3:I25 that you can select items. The selection is done with a CheckBox at each items. I would like the items selected to be listed in column order below the list,ex:A30,31,32 etc.Can you help me, I am not very good with functions or macro. Regards John |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random list place in column order.
I just sent another Request titled " Retrieving data from one form to
another " Thinking that this request was not clear, please ignore the second request. Thank you for your reply I will try it and let you know if I can make it work. Thanks Again "Max" wrote in message ... One play to try .. something along the lines illustrated in this sample: http://www.savefile.com/files/1333415 Extract checkbox selections into col.xls Assume 6 source items are listed in A3:B5, viz: Item1 Item4 Item2 Item5 Item3 Item6 6 checkboxes are drawn from control toolbox, with corresponding link cells in E3:F5 (these are set in Properties view. In design mode, right-click on checkbox choose Properties) In E10: =OFFSET($A$3,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3)) In F10: =IF(OFFSET($E$3,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3)),ROWS($1:1),"") In G10: =IF(ROWS($1:1)COUNT($F$10:$F$15),"",SMALL($F$10:$ F$15,ROWS($1:1))) Select E10:G10, copy down to G15 Then in A10, copied down to A15: =IF(G10="","",INDEX($E$10:$E$15,G10)) CF is applied to results range A10:A15 using formula is: =A10="", formatted accordingly to mask with white font/borderless -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote in message ... Hi Everyone I have a list, A3:I25 that you can select items. The selection is done with a CheckBox at each items. I would like the items selected to be listed in column order below the list,ex:A30,31,32 etc.Can you help me, I am not very good with functions or macro. Regards John |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random list place in column order.
Hi Again
Just to say It works, Thank you Max "John" wrote in message ... I just sent another Request titled " Retrieving data from one form to another " Thinking that this request was not clear, please ignore the second request. Thank you for your reply I will try it and let you know if I can make it work. Thanks Again "Max" wrote in message ... One play to try .. something along the lines illustrated in this sample: http://www.savefile.com/files/1333415 Extract checkbox selections into col.xls Assume 6 source items are listed in A3:B5, viz: Item1 Item4 Item2 Item5 Item3 Item6 6 checkboxes are drawn from control toolbox, with corresponding link cells in E3:F5 (these are set in Properties view. In design mode, right-click on checkbox choose Properties) In E10: =OFFSET($A$3,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3)) In F10: =IF(OFFSET($E$3,MOD(ROWS($1:1)-1,3),INT((ROWS($1:1)-1)/3)),ROWS($1:1),"") In G10: =IF(ROWS($1:1)COUNT($F$10:$F$15),"",SMALL($F$10:$ F$15,ROWS($1:1))) Select E10:G10, copy down to G15 Then in A10, copied down to A15: =IF(G10="","",INDEX($E$10:$E$15,G10)) CF is applied to results range A10:A15 using formula is: =A10="", formatted accordingly to mask with white font/borderless -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote in message ... Hi Everyone I have a list, A3:I25 that you can select items. The selection is done with a CheckBox at each items. I would like the items selected to be listed in column order below the list,ex:A30,31,32 etc.Can you help me, I am not very good with functions or macro. Regards John |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random list place in column order.
Glad to hear that, John.
You're welcome -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John" wrote in message ... Hi Again Just to say It works, Thank you Max |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random list place in column order.
Alternative link:
http://www.freefilehosting.net/download/3b087 Extract checkbox selections into col.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create and extract a random order from list in Excel? | Excel Discussion (Misc queries) | |||
How do I place items in alphabetical order in a column and keep da | Excel Worksheet Functions | |||
extract data from a random list & place in another ordered list | Excel Discussion (Misc queries) | |||
How to sort al list in random order for sampling? | Excel Discussion (Misc queries) | |||
list names in random order in Excel 2002 | Excel Discussion (Misc queries) |