I am using Excel 2000. I would like to create a worksheet that would
automatically hide certain rows depending on a value in a cell in a different
row (or in the same row, if that would work better). I will be sharing
the worksheet with others and I want to make it as user-friendly as possible.

The worksheet would have, say, 3 groups of 26 rows. Some users may only use
6 or 7 rows in one group, and 10 rows in the second group, and perhaps no
rows in
the third group. Another user may use 10 rows in each of the three groups.
Each user will have a different need. I would like to be able to enter a
value into three cells and have the extra rows not appear in each group. I
do not want the user to have to go to the toolbar to hide or filter out rows
that are not used if that can be avoided. Is there any way to do this?

Thanks.

Take a look at Auto Filter.
Click a cell in your database and go Data/Filter/Auto Filter
Rgds,
ScottO

"RJQMAN" wrote in message
...
| I am using Excel 2000. I would like to create a worksheet that would
| automatically hide certain rows depending on a value in a cell in a
different
| row (or in the same row, if that would work better). I will be sharing
| the worksheet with others and I want to make it as user-friendly as
possible.
|
| The worksheet would have, say, 3 groups of 26 rows. Some users may only
use
| 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no
| rows in
| the third group. Another user may use 10 rows in each of the three
groups.
| Each user will have a different need. I would like to be able to enter a
| value into three cells and have the extra rows not appear in each group.
I
| do not want the user to have to go to the toolbar to hide or filter out
rows
| that are not used if that can be avoided. Is there any way to do this?
|
| Thanks.
|

I read the information concerning auto-filter before I made my original post
(sorry I posted twice - I am a novice at these particular boards), but it
still does not appear that I can have the worksheet 1) Do anything
automatically with the auto-filter command, as I believe you have to go to
the toolbar and manually request the filtering (I tested it with some changes
to a reference cell, and it did not seem to pick up the changes automatically
- I had to request it to filter again by going to the tool bar), and 2) If I
understand it correctly, auto-filter enables me to shade a cell, but not hide
a row.

I am no expert for sure, and any and all help is appreciated.

"ScottO" wrote:

Take a look at Auto Filter.
Click a cell in your database and go Data/Filter/Auto Filter
Rgds,
ScottO

"RJQMAN" wrote in message
...
| I am using Excel 2000. I would like to create a worksheet that would
| automatically hide certain rows depending on a value in a cell in a
different
| row (or in the same row, if that would work better). I will be sharing
| the worksheet with others and I want to make it as user-friendly as
possible.
|
| The worksheet would have, say, 3 groups of 26 rows. Some users may only
use
| 6 or 7 rows in one group, and 10 rows in the second group, and perhaps no
| rows in
| the third group. Another user may use 10 rows in each of the three
groups.
| Each user will have a different need. I would like to be able to enter a
| value into three cells and have the extra rows not appear in each group.
I
| do not want the user to have to go to the toolbar to hide or filter out
rows
| that are not used if that can be avoided. Is there any way to do this?
|
| Thanks.
|

My apologies RJQ, I didn't read your post carefully enough.

You're right, the Auto Filter won't do what you want - it requires manual
(or macro driven) intervention each time a cell is changed.

Although not doing exactly what you want, as a work-around you could use
Conditional Formatting to Black Out or otherwise indicate "Out of Bounds"
cells.

Good Luck,
ScottO

"RJQMAN" wrote in message
...
| I read the information concerning auto-filter before I made my original
post
| (sorry I posted twice - I am a novice at these particular boards), but it
| still does not appear that I can have the worksheet 1) Do anything
| automatically with the auto-filter command, as I believe you have to go to
| the toolbar and manually request the filtering (I tested it with some
changes
| to a reference cell, and it did not seem to pick up the changes
automatically
| - I had to request it to filter again by going to the tool bar), and 2) If
I
| understand it correctly, auto-filter enables me to shade a cell, but not
hide
| a row.
|
| I am no expert for sure, and any and all help is appreciated.
|
| "ScottO" wrote:
|
| Take a look at Auto Filter.
| Click a cell in your database and go Data/Filter/Auto Filter
| Rgds,
| ScottO
|
| "RJQMAN" wrote in message
| ...
| | I am using Excel 2000. I would like to create a worksheet that would
| | automatically hide certain rows depending on a value in a cell in a
| different
| | row (or in the same row, if that would work better). I will be
sharing
| | the worksheet with others and I want to make it as user-friendly as
| possible.
| |
| | The worksheet would have, say, 3 groups of 26 rows. Some users may
only
| use
| | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps
no
| | rows in
| | the third group. Another user may use 10 rows in each of the three
| groups.
| | Each user will have a different need. I would like to be able to
enter a
| | value into three cells and have the extra rows not appear in each
group.
| I
| | do not want the user to have to go to the toolbar to hide or filter
out
| rows
| | that are not used if that can be avoided. Is there any way to do
this?
| |
| | Thanks.
| |
|
|
|

Is there a way to drive the filter command with a macro? How would I do this?

"ScottO" wrote:

My apologies RJQ, I didn't read your post carefully enough.

You're right, the Auto Filter won't do what you want - it requires manual
(or macro driven) intervention each time a cell is changed.

Although not doing exactly what you want, as a work-around you could use
Conditional Formatting to Black Out or otherwise indicate "Out of Bounds"
cells.

Good Luck,
ScottO

"RJQMAN" wrote in message
...
| I read the information concerning auto-filter before I made my original
post
| (sorry I posted twice - I am a novice at these particular boards), but it
| still does not appear that I can have the worksheet 1) Do anything
| automatically with the auto-filter command, as I believe you have to go to
| the toolbar and manually request the filtering (I tested it with some
changes
| to a reference cell, and it did not seem to pick up the changes
automatically
| - I had to request it to filter again by going to the tool bar), and 2) If
I
| understand it correctly, auto-filter enables me to shade a cell, but not
hide
| a row.
|
| I am no expert for sure, and any and all help is appreciated.
|
| "ScottO" wrote:
|
| Take a look at Auto Filter.
| Click a cell in your database and go Data/Filter/Auto Filter
| Rgds,
| ScottO
|
| "RJQMAN" wrote in message
| ...
| | I am using Excel 2000. I would like to create a worksheet that would
| | automatically hide certain rows depending on a value in a cell in a
| different
| | row (or in the same row, if that would work better). I will be
sharing
| | the worksheet with others and I want to make it as user-friendly as
| possible.
| |
| | The worksheet would have, say, 3 groups of 26 rows. Some users may
only
| use
| | 6 or 7 rows in one group, and 10 rows in the second group, and perhaps
no
| | rows in
| | the third group. Another user may use 10 rows in each of the three
| groups.
| | Each user will have a different need. I would like to be able to
enter a
| | value into three cells and have the extra rows not appear in each
group.
| I
| | do not want the user to have to go to the toolbar to hide or filter
out
| rows
| | that are not used if that can be avoided. Is there any way to do
this?
| |
| | Thanks.
| |
|
|
|

Try this ...
Assuming that your workbook is called "Book2", and the relevant worksheet is
called "Sheet2", and the range that you want filtered has been named
"Table", and the 'Trigger Cell' has been named "Filtrum", and the column
containing the filter criterion is the first column in the range named as
"Table", then ...

Open the Visual Basic Editor (Go Tools/Macro/Visual Basic Editor)
If the Project Explorer pane isn't visible, go View/Project Explorer
Double Click on VBA Project (Book2)
Double Click on Sheet2
Paste this code into code area (I don't know the proper name)

Private Sub Worksheet_Change(ByVal FilterNum As Range)

Range("Table").AutoFilter Field:=1, Criteria1:=Range("FilterNum")
End Sub


Go back to your worksheet and change the value in FilterNum.

Hopefully you'll see the Auto Filter do it's thing.

Good Luck.
Rgds,
ScottO



"RJQMAN" wrote in message
...
| Is there a way to drive the filter command with a macro? How would I do
this?
|
| "ScottO" wrote:
|
| My apologies RJQ, I didn't read your post carefully enough.
|
| You're right, the Auto Filter won't do what you want - it requires
manual
| (or macro driven) intervention each time a cell is changed.
|
| Although not doing exactly what you want, as a work-around you could use
| Conditional Formatting to Black Out or otherwise indicate "Out of
Bounds"
| cells.
|
| Good Luck,
| ScottO
|
| "RJQMAN" wrote in message
| ...
| | I read the information concerning auto-filter before I made my
original
| post
| | (sorry I posted twice - I am a novice at these particular boards), but
it
| | still does not appear that I can have the worksheet 1) Do anything
| | automatically with the auto-filter command, as I believe you have to
go to
| | the toolbar and manually request the filtering (I tested it with some
| changes
| | to a reference cell, and it did not seem to pick up the changes
| automatically
| | - I had to request it to filter again by going to the tool bar), and
2) If
| I
| | understand it correctly, auto-filter enables me to shade a cell, but
not
| hide
| | a row.
| |
| | I am no expert for sure, and any and all help is appreciated.
| |
| | "ScottO" wrote:
| |
| | Take a look at Auto Filter.
| | Click a cell in your database and go Data/Filter/Auto Filter
| | Rgds,
| | ScottO
| |
| | "RJQMAN" wrote in message
| | ...
| | | I am using Excel 2000. I would like to create a worksheet that
would
| | | automatically hide certain rows depending on a value in a cell in
a
| | different
| | | row (or in the same row, if that would work better). I will be
| sharing
| | | the worksheet with others and I want to make it as user-friendly
as
| | possible.
| | |
| | | The worksheet would have, say, 3 groups of 26 rows. Some users may
| only
| | use
| | | 6 or 7 rows in one group, and 10 rows in the second group, and
perhaps
| no
| | | rows in
| | | the third group. Another user may use 10 rows in each of the
three
| | groups.
| | | Each user will have a different need. I would like to be able to
| enter a
| | | value into three cells and have the extra rows not appear in each
| group.
| | I
| | | do not want the user to have to go to the toolbar to hide or
filter
| out
| | rows
| | | that are not used if that can be avoided. Is there any way to do
| this?
| | |
| | | Thanks.
| | |
| |
| |
| |
|
|
|

Thank you. I will run some tests and see how it goes. I appreciate your
help. I will post here when I get the total problem solved. Other advice is
appreciated as well.

"ScottO" wrote:

Try this ...
Assuming that your workbook is called "Book2", and the relevant worksheet is
called "Sheet2", and the range that you want filtered has been named
"Table", and the 'Trigger Cell' has been named "Filtrum", and the column
containing the filter criterion is the first column in the range named as
"Table", then ...

Open the Visual Basic Editor (Go Tools/Macro/Visual Basic Editor)
If the Project Explorer pane isn't visible, go View/Project Explorer
Double Click on VBA Project (Book2)
Double Click on Sheet2
Paste this code into code area (I don't know the proper name)

Private Sub Worksheet_Change(ByVal FilterNum As Range)

Range("Table").AutoFilter Field:=1, Criteria1:=Range("FilterNum")
End Sub


Go back to your worksheet and change the value in FilterNum.

Hopefully you'll see the Auto Filter do it's thing.

Good Luck.
Rgds,
ScottO



"RJQMAN" wrote in message
...
| Is there a way to drive the filter command with a macro? How would I do
this?
|
| "ScottO" wrote:
|
| My apologies RJQ, I didn't read your post carefully enough.
|
| You're right, the Auto Filter won't do what you want - it requires
manual
| (or macro driven) intervention each time a cell is changed.
|
| Although not doing exactly what you want, as a work-around you could use
| Conditional Formatting to Black Out or otherwise indicate "Out of
Bounds"
| cells.
|
| Good Luck,
| ScottO
|
| "RJQMAN" wrote in message
| ...
| | I read the information concerning auto-filter before I made my
original
| post
| | (sorry I posted twice - I am a novice at these particular boards), but
it
| | still does not appear that I can have the worksheet 1) Do anything
| | automatically with the auto-filter command, as I believe you have to
go to
| | the toolbar and manually request the filtering (I tested it with some
| changes
| | to a reference cell, and it did not seem to pick up the changes
| automatically
| | - I had to request it to filter again by going to the tool bar), and
2) If
| I
| | understand it correctly, auto-filter enables me to shade a cell, but
not
| hide
| | a row.
| |
| | I am no expert for sure, and any and all help is appreciated.
| |
| | "ScottO" wrote:
| |
| | Take a look at Auto Filter.
| | Click a cell in your database and go Data/Filter/Auto Filter
| | Rgds,
| | ScottO
| |
| | "RJQMAN" wrote in message
| | ...
| | | I am using Excel 2000. I would like to create a worksheet that
would
| | | automatically hide certain rows depending on a value in a cell in
a
| | different
| | | row (or in the same row, if that would work better). I will be
| sharing
| | | the worksheet with others and I want to make it as user-friendly
as
| | possible.
| | |
| | | The worksheet would have, say, 3 groups of 26 rows. Some users may
| only
| | use
| | | 6 or 7 rows in one group, and 10 rows in the second group, and
perhaps
| no
| | | rows in
| | | the third group. Another user may use 10 rows in each of the
three
| | groups.
| | | Each user will have a different need. I would like to be able to
| enter a
| | | value into three cells and have the extra rows not appear in each
| group.
| | I
| | | do not want the user to have to go to the toolbar to hide or
filter
| out
| | rows
| | | that are not used if that can be avoided. Is there any way to do
| this?
| | |
| | | Thanks.
| | |
| |
| |
| |
|
|
|