Check Boxes
 

I have a workbook with multiple worksheets. On worksheet 1 I have 10 check
boxes (Cells A1 to A10) that, when checked, links the data in Worksheet 1
(Cells B1 to B10) to Cells B1 to B10 on Worksheet 2. Cell B1 on Worsheet 1 is
linked to Cell B2 on Worksheet 2 and so on. The formula I used (that someone
in the Excel Discussion Group gave me) is
=IF(Worksheet1!A1<TRUE,"",IF(Worksheet1!B1="","", Worksheet!B1)). I put this
formula in cell B1 or Worksheet 2 and it works fine.

My question is, is there any way I can set up Worksheet 2 so that if any of
the 10 checkboxes on Worksheet 1 are checked (let's say three of them), the
data that corresponds with those checkboxes will show up in the first three
cells on Worksheet 2? Put another way, If I have 100 checkboxes on Worksheet
1 (with specific data linked to each one) and I only check 3 of them, is
there anyway to make the data linked to those 3 checkboxes show up in the the
first 3 cells (A1, A2, A3) of Worksheet 2? If so, how would I do that. Any
assistance would be greatly appreciated.

Thanks

Check Boxes
 

Not sure if I understand your question
If I do you need a macro like this that when you check Checkbox1 you can
put the data into cell A1.
You would have to do this for all checkboxes
Sub checkBox1()
Worksheets("Sheet2").Range("A1").Value _
= "CheckBoxChecked"
End Sub

"Joe" wrote:

I have a workbook with multiple worksheets. On worksheet 1 I have 10 check
boxes (Cells A1 to A10) that, when checked, links the data in Worksheet 1
(Cells B1 to B10) to Cells B1 to B10 on Worksheet 2. Cell B1 on Worsheet 1 is
linked to Cell B2 on Worksheet 2 and so on. The formula I used (that someone
in the Excel Discussion Group gave me) is
=IF(Worksheet1!A1<TRUE,"",IF(Worksheet1!B1="","", Worksheet!B1)). I put this
formula in cell B1 or Worksheet 2 and it works fine.

My question is, is there any way I can set up Worksheet 2 so that if any of
the 10 checkboxes on Worksheet 1 are checked (let's say three of them), the
data that corresponds with those checkboxes will show up in the first three
cells on Worksheet 2? Put another way, If I have 100 checkboxes on Worksheet
1 (with specific data linked to each one) and I only check 3 of them, is
there anyway to make the data linked to those 3 checkboxes show up in the the
first 3 cells (A1, A2, A3) of Worksheet 2? If so, how would I do that. Any
assistance would be greatly appreciated.

Thanks

Check Boxes
 

Try this:

I'm assuming that your check boxes are linked to the cells they're "in". For
example, the check box "in" cell A1 is linked to cell A1.

Enter this array formula** in Sheet2 A1:

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!A$1:A$10,TRUE),IN DEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!A$1:A$10,ROW(S heet1!B$1:B$10)-MIN(ROW(Sheet1!B$1:B$10))+1),ROWS(A$1:A1))),"")

Copy down to a number of cells that equals the number of check boxes you
have. The above formula assumes you have 10 check boxes in the range Sheet1
A1:A10.

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



--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
I have a workbook with multiple worksheets. On worksheet 1 I have 10 check
boxes (Cells A1 to A10) that, when checked, links the data in Worksheet 1
(Cells B1 to B10) to Cells B1 to B10 on Worksheet 2. Cell B1 on Worsheet 1
is
linked to Cell B2 on Worksheet 2 and so on. The formula I used (that
someone
in the Excel Discussion Group gave me) is
=IF(Worksheet1!A1<TRUE,"",IF(Worksheet1!B1="","", Worksheet!B1)). I put
this
formula in cell B1 or Worksheet 2 and it works fine.

My question is, is there any way I can set up Worksheet 2 so that if any
of
the 10 checkboxes on Worksheet 1 are checked (let's say three of them),
the
data that corresponds with those checkboxes will show up in the first
three
cells on Worksheet 2? Put another way, If I have 100 checkboxes on
Worksheet
1 (with specific data linked to each one) and I only check 3 of them, is
there anyway to make the data linked to those 3 checkboxes show up in the
the
first 3 cells (A1, A2, A3) of Worksheet 2? If so, how would I do that. Any
assistance would be greatly appreciated.

Thanks

Check Boxes
 

Thank you very, very much!

"T. Valko" wrote:

Try this:

I'm assuming that your check boxes are linked to the cells they're "in". For
example, the check box "in" cell A1 is linked to cell A1.

Enter this array formula** in Sheet2 A1:

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!A$1:A$10,TRUE),IN DEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!A$1:A$10,ROW(S heet1!B$1:B$10)-MIN(ROW(Sheet1!B$1:B$10))+1),ROWS(A$1:A1))),"")

Copy down to a number of cells that equals the number of check boxes you
have. The above formula assumes you have 10 check boxes in the range Sheet1
A1:A10.

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



--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
I have a workbook with multiple worksheets. On worksheet 1 I have 10 check
boxes (Cells A1 to A10) that, when checked, links the data in Worksheet 1
(Cells B1 to B10) to Cells B1 to B10 on Worksheet 2. Cell B1 on Worsheet 1
is
linked to Cell B2 on Worksheet 2 and so on. The formula I used (that
someone
in the Excel Discussion Group gave me) is
=IF(Worksheet1!A1<TRUE,"",IF(Worksheet1!B1="","", Worksheet!B1)). I put
this
formula in cell B1 or Worksheet 2 and it works fine.

My question is, is there any way I can set up Worksheet 2 so that if any
of
the 10 checkboxes on Worksheet 1 are checked (let's say three of them),
the
data that corresponds with those checkboxes will show up in the first
three
cells on Worksheet 2? Put another way, If I have 100 checkboxes on
Worksheet
1 (with specific data linked to each one) and I only check 3 of them, is
there anyway to make the data linked to those 3 checkboxes show up in the
the
first 3 cells (A1, A2, A3) of Worksheet 2? If so, how would I do that. Any
assistance would be greatly appreciated.

Thanks

Check Boxes
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
Thank you very, very much!

"T. Valko" wrote:

Try this:

I'm assuming that your check boxes are linked to the cells they're "in".
For
example, the check box "in" cell A1 is linked to cell A1.

Enter this array formula** in Sheet2 A1:

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!A$1:A$10,TRUE),IN DEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!A$1:A$10,ROW(S heet1!B$1:B$10)-MIN(ROW(Sheet1!B$1:B$10))+1),ROWS(A$1:A1))),"")

Copy down to a number of cells that equals the number of check boxes you
have. The above formula assumes you have 10 check boxes in the range
Sheet1
A1:A10.

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



--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
I have a workbook with multiple worksheets. On worksheet 1 I have 10
check
boxes (Cells A1 to A10) that, when checked, links the data in Worksheet
1
(Cells B1 to B10) to Cells B1 to B10 on Worksheet 2. Cell B1 on
Worsheet 1
is
linked to Cell B2 on Worksheet 2 and so on. The formula I used (that
someone
in the Excel Discussion Group gave me) is
=IF(Worksheet1!A1<TRUE,"",IF(Worksheet1!B1="","", Worksheet!B1)). I put
this
formula in cell B1 or Worksheet 2 and it works fine.

My question is, is there any way I can set up Worksheet 2 so that if
any
of
the 10 checkboxes on Worksheet 1 are checked (let's say three of them),
the
data that corresponds with those checkboxes will show up in the first
three
cells on Worksheet 2? Put another way, If I have 100 checkboxes on
Worksheet
1 (with specific data linked to each one) and I only check 3 of them,
is
there anyway to make the data linked to those 3 checkboxes show up in
the
the
first 3 cells (A1, A2, A3) of Worksheet 2? If so, how would I do that.
Any
assistance would be greatly appreciated.

Thanks