Tricky problem must solve without VBA !

davep

Board Regular
Joined
Nov 2, 2002
Messages
68
I must try to solve this tricky problem without using VBA if possible.

I have two worksheets. On worksheet 2 I have 6 rows (4:9) and each row has a checkbox and data in 7 columns. When I check one of the checkboxes, I need my row of data to appear in worksheet 1.

Thanks in advance,
Dave
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Link each checkbox to a cell on sheet1(let's say A1 for the first checkbox), then on your second sheet enter the following formula in the appropriate cells :-

=IF(Sheet1!$A$1,Sheet1!B1,"")
 
Upvote 0
You could have a sheet linked to the data (using the forumula in the previous post) and then sort it so you will see all the ones that are checked at the top - if thats what you want.

HTH
 
Upvote 0
Thanks to everyone for replying.

Pear, can you explain what you mean by your post.

Thanks,
Dave
 
Upvote 0
To link a cell to your checkbox:

1. Activate the Control Toolbox and click the Design icon (top left, looks like a geometry set).
2. Right click the checkbox and choose Properties.
3. Set the LinkedCell property to eg A1 and close the properties window.
4. Click the Design icon again.

Now when the checkbox is checked cell A1 will contain TRUE, and when it is unchecked it will contain FALSE.

Then you can write formulas on Sheet1 to get the data on Sheet2 if the checkbox is checked, eg

=IF(Sheet2!$A$1,Sheet2!B1,"")

which shows nothing if the checkbox is unchecked.
 
Upvote 0
Thanks to Pear and Andrew for the replies, that worked well.

I do have another related problem that needs to be solved without the use of VBA code.

On Sheet2 I have 6 rows (4:9) with a check box for each row. As already explained, when I select a check box the row will appear on sheet1.

Here is the problem. If I select the check boxes for rows 4 and 9, how do I get those rows to appear one after each other on sheets 1 in the correct order selected (i.e. row 4 first and row 9 immediately after it)???

Thanks in advance,
Dave
 
Upvote 0
1. Enter this formula in cell A1 on Sheet1:

=IF(ISNA(MATCH(TRUE,Sheet2!A1:A9,0)),0,MATCH(TRUE,Sheet2!A1:A9,0))

2. Enter this formula in cell A2 on Sheet1:

=IF(A1,IF(ISNA(MATCH(TRUE,OFFSET(Sheet2!A$1:A$9,A1,0),0)),0,MATCH(TRUE,OFFSET(Sheet2!A$1:A$9,A1,0),0)+A1),0)

and copy to A3:A9

3. Enter this formula in cell B1 on Sheet1:

=IF(A1,INDEX(Sheet2!B$1:B$9,A1,0),"")

and copy to B2:B9
 
Upvote 0
This is another problem following on from the last that has me well and truly stumped.

On sheet1 I have a second table that will be dependant on the values in table 1 (i.e. the value selected using the check boxes) and on three specific criteria.

If the values satisfy all criteria, three messages will be concatenated and displayed. If the values only satisfy two criteria, then two messages will be concatenated etc.

Again, only data selected in table 1 will be compared against the three criteria and displayed in table 2.

Thanks in advance,
Dave
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top