Extracting Rows to Make A Totals Sheet

[Guest]

I have multiple worksheets and any time something is entered on one of them I
want it to show up on my totals page. As an example we have mulitple sheets
with the headings. They must be all in separate sheets and can not be
combined into one.

First sheet

[Voucher Number] [Branch] [Invoice] [Customer]
12344 Fred 12345 Frank

Second sheet

[Voucher Number] [Branch] [Invoice] [Customer]
2222 Sussex 0003 Sam


Totals Sheet
[Voucher Number] [Branch] [Invoice] [Customer]
12344 Fred 12345 Frank
2222 Sussex 0003 Sam

 

[Guest]

Well, Savage, IMO you're approaching this from the wrong end of the
telescope. If you originally put all the data on a single sheet you can very
easily use Data->Filter->Advanced filters to separate the data. You could
even put it into an Access table and use the Data->Get External Data options
to pull subsets of it into a spreadsheet as desired.

If you stick with your approach, you will need worksheet event code in VBA
for each sheet to copy new entries from each sheet to your 'aggregate' sheet.
Even with such code, how do you ensure that the same data desn't get copied
anew when somebody edits a row? Data integrity becomes much, MUCH harder to
ensure using your approach.

 

[David Hilberg]

If you stick with your approach, you will need worksheet event code in VBA
for each sheet to copy new entries from each sheet to your 'aggregate' sheet.
Even with such code, how do you ensure that the same data desn't get copied
anew when somebody edits a row?

If the event code recopied all data from all sheets whenever the Totals
sheet was visited, duplicate records would be avoided. Data would
probably end up clumped by sheet, of course. And there could be a slight
delay, which might become annoying if flipping back and forth.

- David

I have multiple worksheets and any time something is entered on one of them I
want it to show up on my totals page. As an example we have mulitple sheets
with the headings. They must be all in separate sheets and can not be
combined into one.

First sheet

[Voucher Number] [Branch] [Invoice] [Customer]
12344 Fred 12345 Frank

Second sheet

[Voucher Number] [Branch] [Invoice] [Customer]
2222 Sussex 0003 Sam


Totals Sheet
[Voucher Number] [Branch] [Invoice] [Customer]
12344 Fred 12345 Frank
2222 Sussex 0003 Sam

 

[Guest]

For a pure formulas-automated play which works to pull in and stack data from
up to 12 identically structured individual sheets (this would be your
individual voucher sheets) into a single summary/master sheet (your Totals
sheet), you might wish to try this sample from my archives (nicely rendered,
full details inside):

http://www.savefile.com/files/236284
Auto summarize n stack lines from 12 primary sheets.zip

The desired stacking sequence for data from the 12 individual sheets within
the summary sheet can be defined easily. Just ensure that the sheetnames
entered within the summary sheet's R5:AC5 are consistent with those on the
actual sheet tabs, ie match exactly, except for case.

Data will be stacked continuously, all neatly bunched at the top, w/o any
intervening blank rows. You could then apply autofilter on the stacked
summary table.

As-is, the sample construct caters for a max of 125 lines/rows expected per
indiv. sheet x 12 sheets = 1500 stacked rows (max) in the summary

 

[Guest]

Thanks!! Exactly what I needed.

For a pure formulas-automated play which works to pull in and stack data from
up to 12 identically structured individual sheets (this would be your
individual voucher sheets) into a single summary/master sheet (your Totals
sheet), you might wish to try this sample from my archives (nicely rendered,
full details inside):

http://www.savefile.com/files/236284
Auto summarize n stack lines from 12 primary sheets.zip

The desired stacking sequence for data from the 12 individual sheets within
the summary sheet can be defined easily. Just ensure that the sheetnames
entered within the summary sheet's R5:AC5 are consistent with those on the
actual sheet tabs, ie match exactly, except for case.

Data will be stacked continuously, all neatly bunched at the top, w/o any
intervening blank rows. You could then apply autofilter on the stacked
summary table.

As-is, the sample construct caters for a max of 125 lines/rows expected per
indiv. sheet x 12 sheets = 1500 stacked rows (max) in the summary

---

I have multiple worksheets and any time something is entered on one of them I
want it to show up on my totals page. As an example we have mulitple sheets
with the headings. They must be all in separate sheets and can not be
combined into one.

First sheet

[Voucher Number] [Branch] [Invoice] [Customer]
12344 Fred 12345 Frank

Second sheet

[Voucher Number] [Branch] [Invoice] [Customer]
2222 Sussex 0003 Sam


Totals Sheet
[Voucher Number] [Branch] [Invoice] [Customer]
12344 Fred 12345 Frank
2222 Sussex 0003 Sam

 

[Max]

welcome, Savage