Check boxes or List boxes?

**NickH** · 03-12-2008, 01:36 PM

I was hoping to get some help with my query. I have a list of companies in A1-A10 (sheet 1), what I was looking to do is either setup checkboxes for each company or a list box that contains all 10 companies.

What I'm hoping to accomplish to to take the companies selected and put them on a second sheet.

So Let's say:

A1=Joe's Candy Shop
A2=Mom's Candy Shop
A3=Nick's Candy Shop
A4=Harry's Candy Shop, etc.

Now, I select Joe's Candy Shop and Harry's Candy Shop, I would like for them to appear on Sheet 2, in Column A. So A1 would equal Joe's Candy Shop and A2 would equal Harry's Candy Shop, order doesn't matter.

I started using checkboxes and linking them to cells and then using the true/false in the linked cells to get what I needed, but I can't figure out how to accomplish this.

Any help would be appreciated, thanks,

Nick

**Mark@Work** · 03-12-2008, 02:30 PM

Someone will probably come up with a far better way that this, but I think it will work...

Set up a column (in sheet1) that has ROW() if the relevant company is checked, or "" if it is not.
Lets assume you use column X.
Then (in sheet2) use SMALL(Sheet1!X:X,ROW()) to construct a column (lets use Y)
containing only the NONBLANK values from Sheet1 column XX
Then set:
Sheet2!A1 to =INDEX(SHEET1!A:A,$Y1)
Sheet2!A2 to =INDEX(SHEET1!A:A,$Y2)
Sheet2!B1 to =INDEX(SHEET1!B:B,$Y1)
etc...

Mark.

**NickH** · 03-13-2008, 10:31 AM

Mark-Thanks for the reply. Your response prompted another question. I have the check boxes on the sheet with the names associated with the check box. I don't have the names actually in A1-A10, but the check boxes are physically located in those cells. If I'm right, Excel doesn't actually put the check boxes in the cells they are on or near, but it's like they are on another layer on top of the spreadsheet

I probably should re-phrase my question. Should I physicallly have the names in A1-A10 and a check box next to them in order for your formula to work?

Thanks for being patient, I'm a newbie in terms of doing something like this.

Nick

**Mark@Work** · 03-17-2008, 02:56 PM

Sorry I haven't replied earlier (buzy elsewhere)
I didn't quite realize what you were trying to do with my earlier posts.
I dont use the forms facilities much so I'm not realy familiar with them,
but I think your last post more or less sums up the situation.
I had a quick play with check boxes and they seem to just be a way of getting TRUE or FALSE into a chosen cell.
The check box position is entirely unconnected with the cell it controls.
The text displayed in the text box is not available for use in other cells.
Thus to get the effect I think you want, set up a column with the company names (one to a row)
Carefully position the check boxes alongside each name and use them to set the corresponding cell in the next column (to true of false)
depending on the value of that cell you can set a third column to be ROW() or ""
A bit scrappy, but about the best I can come up with.