On one sheet I have a long list of items which I need my users to scroll down
and, when needed, fill in a value next to the appropriate item in the list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only shows
those list items which have an input against them.

The bit I cant do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

use datafilterautofiltercopy the filtered

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
On one sheet I have a long list of items which I need my users to scroll
down
and, when needed, fill in a value next to the appropriate item in the
list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only shows
those list items which have an input against them.

The bit I cant do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

is there anyway the summary list can be populated automatically? i have a
lot of these forms. Also the users need the summary list themselves, without
intervention from me...if possible.

Many thanks for the quick response though.



"Don Guillett" wrote:

use datafilterautofiltercopy the filtered

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
On one sheet I have a long list of items which I need my users to scroll
down
and, when needed, fill in a value next to the appropriate item in the
list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only shows
those list items which have an input against them.

The bit I cant do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

The question now is; What do you mean by "automatically? Specifically, what
do you want to use as the trigger that induces Excel to do this for you?
For instance, Excel can do this each and every time that an entry is made in
that column that the users use for input. Or Excel can do this whenever the
file is saved. Or closed.. Or opened. HTH Otto
"Zebediah" wrote in message
...
is there anyway the summary list can be populated automatically? i have a
lot of these forms. Also the users need the summary list themselves,
without
intervention from me...if possible.

Many thanks for the quick response though.



"Don Guillett" wrote:

use datafilterautofiltercopy the filtered

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
On one sheet I have a long list of items which I need my users to
scroll
down
and, when needed, fill in a value next to the appropriate item in the
list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only
shows
those list items which have an input against them.

The bit I can't do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

Otto - By Automatically i would like the trigger to be as an entry is made.
Sorry i didn't clarify this before, and my apologies for not spotting your
reply.

Best regards


"Otto Moehrbach" wrote:

The question now is; What do you mean by "automatically? Specifically, what
do you want to use as the trigger that induces Excel to do this for you?
For instance, Excel can do this each and every time that an entry is made in
that column that the users use for input. Or Excel can do this whenever the
file is saved. Or closed.. Or opened. HTH Otto
"Zebediah" wrote in message
...
is there anyway the summary list can be populated automatically? i have a
lot of these forms. Also the users need the summary list themselves,
without
intervention from me...if possible.

Many thanks for the quick response though.



"Don Guillett" wrote:

use datafilterautofiltercopy the filtered

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
On one sheet I have a long list of items which I need my users to
scroll
down
and, when needed, fill in a value next to the appropriate item in the
list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only
shows
those list items which have an input against them.

The bit I can't do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

If desired, send your workbook to my address below along with a complete
explanation and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
is there anyway the summary list can be populated automatically? i have a
lot of these forms. Also the users need the summary list themselves,
without
intervention from me...if possible.

Many thanks for the quick response though.



"Don Guillett" wrote:

use datafilterautofiltercopy the filtered

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
On one sheet I have a long list of items which I need my users to
scroll
down
and, when needed, fill in a value next to the appropriate item in the
list.

Most of the list items will not need anything inputting against them.

Then on a different sheet I need a nice tidy summary list which only
shows
those list items which have an input against them.

The bit I cant do is getting these few items to display (on a separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?

This macro will do what you want. It will fire whenever an entry is made in
Column B of the active sheet. Note that I named the second sheet "Two".
Place this macro in the sheet module of the sheet that holds your data and
into which the user will make an entry in Column B. To access that module,
right-click on the sheet tab and select View Code. Paste this macro into
that module. "X" out of the module to return to your sheet. This macro
depends on the contents of Column A to determine the extent of your data.
View this message in full screen before you copy to avoid line wrapping.
HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColA As Range
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
With Sheets("Two")
.Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(,
1)).ClearContents
Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
rColA.Resize(, 2).AutoFilter
rColA.Resize(, 2).AutoFilter Field:=2, Criteria1:="<"
rColA.Resize(, 2).SpecialCells(xlCellTypeVisible).Copy
..Range("A1")
End With
End If
End Sub
"Zebediah" wrote in message
...
Otto - By Automatically i would like the trigger to be as an entry is
made.
Sorry i didn't clarify this before, and my apologies for not spotting your
reply.

Best regards


"Otto Moehrbach" wrote:

The question now is; What do you mean by "automatically? Specifically,
what
do you want to use as the trigger that induces Excel to do this for you?
For instance, Excel can do this each and every time that an entry is made
in
that column that the users use for input. Or Excel can do this whenever
the
file is saved. Or closed.. Or opened. HTH Otto
"Zebediah" wrote in message
...
is there anyway the summary list can be populated automatically? i
have a
lot of these forms. Also the users need the summary list themselves,
without
intervention from me...if possible.

Many thanks for the quick response though.



"Don Guillett" wrote:

use datafilterautofiltercopy the filtered

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zebediah" wrote in message
...
On one sheet I have a long list of items which I need my users to
scroll
down
and, when needed, fill in a value next to the appropriate item in
the
list.

Most of the list items will not need anything inputting against
them.

Then on a different sheet I need a nice tidy summary list which only
shows
those list items which have an input against them.

The bit I can't do is getting these few items to display (on a
separate
sheet) in a nice neat all together, contiguous way - ie with no gaps

any ideas?