Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Rows to Make A Totals Sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Rows to Make A Totals Sheet
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. "Savage" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Rows to Make A Totals Sheet
Duke Carey wrote:
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 "Savage" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Rows to Make A Totals Sheet
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Savage" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Rows to Make A Totals Sheet
Thanks!! Exactly what I needed.
"Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Savage" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Rows to Make A Totals Sheet
welcome, Savage
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Savage" wrote in message ... Thanks!! Exactly what I needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting rows from worksheets to make a totals sheet | Excel Discussion (Misc queries) | |||
How to make mtd and wtd totals on dates | Excel Worksheet Functions | |||
extracting totals from within a spreadsheet | New Users to Excel | |||
extracting totals from within a spreadsheet | New Users to Excel | |||
make a macro to move specific rows to another sheet | Excel Worksheet Functions |