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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
L

Legacy 11779

Guest
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,"")
 

Blacky

Board Regular
Joined
Jun 9, 2002
Messages
105
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
 

davep

Board Regular
Joined
Nov 2, 2002
Messages
68
Thanks to everyone for replying.

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

Thanks,
Dave
 

davep

Board Regular
Joined
Nov 2, 2002
Messages
68

ADVERTISEMENT

Can anyone else suggest a solution to this problem?

Thanks in advance,
Dave
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

davep

Board Regular
Joined
Nov 2, 2002
Messages
68

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

davep

Board Regular
Joined
Nov 2, 2002
Messages
68
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,519
Members
413,996
Latest member
mabelO

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
Top