Combining Lists/Summary Page
 

,Hello,
I have a workbook with multiple sheets. Each sheet has 4 columns and
depending on the amount of data, multiple rows (usually between 1 and 20). I
would like to create a summary page that would search all of the worksheets
for data entered and combine all of these rows of data (no need to search for
duplicates - there won't be any) on one main sheet. Is this possible? It
seems like an array formula may do the trick but I'm not too deft at writing
my own arrays.

Thank you in advance!

Combining Lists/Summary Page
 

Please give more detail as to how the data is set out in the sheets
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SLW612" wrote in message
...
,Hello,
I have a workbook with multiple sheets. Each sheet has 4 columns and
depending on the amount of data, multiple rows (usually between 1 and 20).
I
would like to create a summary page that would search all of the
worksheets
for data entered and combine all of these rows of data (no need to search
for
duplicates - there won't be any) on one main sheet. Is this possible? It
seems like an array formula may do the trick but I'm not too deft at
writing
my own arrays.

Thank you in advance!

Combining Lists/Summary Page
 

Example (I hope this comes out right):

Sheet 1 ("AG")
Col. A is vendor name, B is vendor ID (always 5 digits), C is date, and D is
notes.
A B C D
1 ABC Co. 01234 8/17/07 Ships directly from
warehouse
2 DEF Co. 12345 9/1/07 Does not need a PO
3 GHI Co. 23456 8/13/07 Always double
quantities
4 JKL Co. 34567 8/22/07 Verify payment first

Sheet 2 ("CH")
A B C D
1 Hedy Co. 45678 7/16/07 Qty 10 each
2 Sign Co. 56789 9/12/07 Speak with Ted
3 Bunt Co. 67890 8/02/07 Add extra for
shipping
4 LFT Co. 78901 9/02/07 Last resort vendor


Each sheet is for a different product, so none of the data will be the same
as on another sheet (e.g. ABC co. only does product "AG" etc).

I have 10 sheets - names are AG, CH, GE, GI, RM, SC, WC, CC, OG, WN. They
are all set up the same but obviously have different data. I was hoping that
in addition to keeping the entries on every sheet as entered, when an entry
was typed onto each page I want it to also populate on a Summary page, and
look something like this:

A B C D
1 ABC Co. 01234 8/17/07 Ships directly from
warehouse
2 DEF Co. 12345 9/1/07 Does not need a PO
3 GHI Co. 23456 8/13/07 Always double
quantities
4 JKL Co. 34567 8/22/07 Verify payment first
5 Hedy Co. 45678 7/16/07 Qty 10 each
6 Sign Co. 56789 9/12/07 Speak with Ted
7 Bunt Co. 67890 8/02/07 Add extra for
shipping
8 LFT Co. 78901 9/02/07 Last resort vendor

I'll settle for them all being on one page, but if they could be in
alphabetical order by Vendor that would be great too. The trick I don't know
how to do is to get the Summary page to recognize that an entry has been
typed on one of the other sheets, and copy that information to the next
available row on Summary page.

I hope this makes sense! Thanks


"Bernard Liengme" wrote:

Please give more detail as to how the data is set out in the sheets
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SLW612" wrote in message
...
,Hello,
I have a workbook with multiple sheets. Each sheet has 4 columns and
depending on the amount of data, multiple rows (usually between 1 and 20).
I
would like to create a summary page that would search all of the
worksheets
for data entered and combine all of these rows of data (no need to search
for
duplicates - there won't be any) on one main sheet. Is this possible? It
seems like an array formula may do the trick but I'm not too deft at
writing
my own arrays.

Thank you in advance!

Combining Lists/Summary Page
 

That was an excellent explanation!
However, to do what you want would take some fancy programming. I am hoping
someone with more VBA skill will answer you. If not I suggest you post this
message on the news:microsoft.public.excel.programming newsgroup.

Database managers are always wary of having the same data stored twice.
Perhaps you do not need the summary sheet. It could be that there is another
way to generate the data (from the other sheets) at the time when you want a
specific report.

You task is almost looking like an Access project.

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SLW612" wrote in message
...
Example (I hope this comes out right):

Sheet 1 ("AG")
Col. A is vendor name, B is vendor ID (always 5 digits), C is date, and D
is
notes.
A B C D
1 ABC Co. 01234 8/17/07 Ships directly
from
warehouse
2 DEF Co. 12345 9/1/07 Does not need a
PO
3 GHI Co. 23456 8/13/07 Always double
quantities
4 JKL Co. 34567 8/22/07 Verify payment
first

Sheet 2 ("CH")
A B C D
1 Hedy Co. 45678 7/16/07 Qty 10 each
2 Sign Co. 56789 9/12/07 Speak with Ted
3 Bunt Co. 67890 8/02/07 Add extra for
shipping
4 LFT Co. 78901 9/02/07 Last resort
vendor


Each sheet is for a different product, so none of the data will be the
same
as on another sheet (e.g. ABC co. only does product "AG" etc).

I have 10 sheets - names are AG, CH, GE, GI, RM, SC, WC, CC, OG, WN. They
are all set up the same but obviously have different data. I was hoping
that
in addition to keeping the entries on every sheet as entered, when an
entry
was typed onto each page I want it to also populate on a Summary page, and
look something like this:

A B C D
1 ABC Co. 01234 8/17/07 Ships directly
from
warehouse
2 DEF Co. 12345 9/1/07 Does not need a
PO
3 GHI Co. 23456 8/13/07 Always double
quantities
4 JKL Co. 34567 8/22/07 Verify payment
first
5 Hedy Co. 45678 7/16/07 Qty 10 each
6 Sign Co. 56789 9/12/07 Speak with Ted
7 Bunt Co. 67890 8/02/07 Add extra for
shipping
8 LFT Co. 78901 9/02/07 Last resort
vendor

I'll settle for them all being on one page, but if they could be in
alphabetical order by Vendor that would be great too. The trick I don't
know
how to do is to get the Summary page to recognize that an entry has been
typed on one of the other sheets, and copy that information to the next
available row on Summary page.

I hope this makes sense! Thanks


"Bernard Liengme" wrote:

Please give more detail as to how the data is set out in the sheets
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SLW612" wrote in message
...
,Hello,
I have a workbook with multiple sheets. Each sheet has 4 columns and
depending on the amount of data, multiple rows (usually between 1 and
20).
I
would like to create a summary page that would search all of the
worksheets
for data entered and combine all of these rows of data (no need to
search
for
duplicates - there won't be any) on one main sheet. Is this possible?
It
seems like an array formula may do the trick but I'm not too deft at
writing
my own arrays.

Thank you in advance!

Combining Lists/Summary Page
 

I wouldn't say that my programming skills were any better than Bernard's but
put this code in *each* sheet's sheet module and it should copy the entered
data and sort it on the Summary sheet.

It assumes that you always have something in the comments column Column D
and that you have labels in row 1.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

With Sheets("Summary")
rLastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
End With

Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy _
Destination:=Sheets("Summary").Cells(rLastRow, 1)

With Sheets("Summary")
.Range(.Cells(1, 1), .Cells(rLastRow + 1, 4)).Sort
Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

End Sub

It works for may but you still may better of in the programming group.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Bernard Liengme" wrote in message
...
That was an excellent explanation!
However, to do what you want would take some fancy programming. I am
hoping someone with more VBA skill will answer you. If not I suggest you
post this message on the news:microsoft.public.excel.programming
newsgroup.

Database managers are always wary of having the same data stored twice.
Perhaps you do not need the summary sheet. It could be that there is
another way to generate the data (from the other sheets) at the time when
you want a specific report.

You task is almost looking like an Access project.

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SLW612" wrote in message
...
Example (I hope this comes out right):

Sheet 1 ("AG")
Col. A is vendor name, B is vendor ID (always 5 digits), C is date, and D
is
notes.
A B C D
1 ABC Co. 01234 8/17/07 Ships directly
from
warehouse
2 DEF Co. 12345 9/1/07 Does not need a
PO
3 GHI Co. 23456 8/13/07 Always double
quantities
4 JKL Co. 34567 8/22/07 Verify payment
first

Sheet 2 ("CH")
A B C D
1 Hedy Co. 45678 7/16/07 Qty 10 each
2 Sign Co. 56789 9/12/07 Speak with Ted
3 Bunt Co. 67890 8/02/07 Add extra for
shipping
4 LFT Co. 78901 9/02/07 Last resort
vendor


Each sheet is for a different product, so none of the data will be the
same
as on another sheet (e.g. ABC co. only does product "AG" etc).

I have 10 sheets - names are AG, CH, GE, GI, RM, SC, WC, CC, OG, WN. They
are all set up the same but obviously have different data. I was hoping
that
in addition to keeping the entries on every sheet as entered, when an
entry
was typed onto each page I want it to also populate on a Summary page,
and
look something like this:

A B C D
1 ABC Co. 01234 8/17/07 Ships directly
from
warehouse
2 DEF Co. 12345 9/1/07 Does not need a
PO
3 GHI Co. 23456 8/13/07 Always double
quantities
4 JKL Co. 34567 8/22/07 Verify payment
first
5 Hedy Co. 45678 7/16/07 Qty 10 each
6 Sign Co. 56789 9/12/07 Speak with Ted
7 Bunt Co. 67890 8/02/07 Add extra for
shipping
8 LFT Co. 78901 9/02/07 Last resort
vendor

I'll settle for them all being on one page, but if they could be in
alphabetical order by Vendor that would be great too. The trick I don't
know
how to do is to get the Summary page to recognize that an entry has been
typed on one of the other sheets, and copy that information to the next
available row on Summary page.

I hope this makes sense! Thanks


"Bernard Liengme" wrote:

Please give more detail as to how the data is set out in the sheets
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"SLW612" wrote in message
...
,Hello,
I have a workbook with multiple sheets. Each sheet has 4 columns and
depending on the amount of data, multiple rows (usually between 1 and
20).
I
would like to create a summary page that would search all of the
worksheets
for data entered and combine all of these rows of data (no need to
search
for
duplicates - there won't be any) on one main sheet. Is this possible?
It
seems like an array formula may do the trick but I'm not too deft at
writing
my own arrays.

Thank you in advance!