| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Copying Data
Dear all,
I've a workbook with several worksheets, namely "Jan06", "Feb06"......and "Summary". This Summary worksheet consolidates all data from columns A, B & E of all other worksheets in the same workbook(they are identical). Every time when I filled in all records for that month, I've to copy the data from columns A, B & E to the "Summary" worksheet for further analysis purpose. Is there a macro which can copy those required data from any of the worksheets automatically to the first blank row in the "Summary" worksheet? If yes, please write me that macro. Thanks in advance. Best regards. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Perhaps you don't need a macro to do it! Place this formula in Summary!A2 and
copy it to the other columns! Make sure that sheets "Jan06", "Feb06"...... are after each other! =SUM(Jan06:Feb06!A2) Regards, Stefi €˛Freshman€¯ ezt Ć*rta: Dear all, I've a workbook with several worksheets, namely "Jan06", "Feb06"......and "Summary". This Summary worksheet consolidates all data from columns A, B & E of all other worksheets in the same workbook(they are identical). Every time when I filled in all records for that month, I've to copy the data from columns A, B & E to the "Summary" worksheet for further analysis purpose. Is there a macro which can copy those required data from any of the worksheets automatically to the first blank row in the "Summary" worksheet? If yes, please write me that macro. Thanks in advance. Best regards. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Hi Stefi,
Thanks for your help. May be I haven't raised my question clear. Sorry for the confusion. My question is not going to total all values in A1 of other worksheets to cell A1 in Summary worksheet. I want to copy every record in columns A, B & E of every worksheet to the Summary worksheet then I use them for analysis in a pivot table. Since the records are filled in month after month, so I want the completed records of the current month can be automatically copy to the row under the last record in the Summary worksheet, i.e. May06's records under Apr06's record. I hope this can make my question clear. Can you help in this aspect? Thanks once again. Regards. "Stefi" wrote: Perhaps you don't need a macro to do it! Place this formula in Summary!A2 and copy it to the other columns! Make sure that sheets "Jan06", "Feb06"...... are after each other! =SUM(Jan06:Feb06!A2) Regards, Stefi €˛Freshman€¯ ezt Ć*rta: Dear all, I've a workbook with several worksheets, namely "Jan06", "Feb06"......and "Summary". This Summary worksheet consolidates all data from columns A, B & E of all other worksheets in the same workbook(they are identical). Every time when I filled in all records for that month, I've to copy the data from columns A, B & E to the "Summary" worksheet for further analysis purpose. Is there a macro which can copy those required data from any of the worksheets automatically to the first blank row in the "Summary" worksheet? If yes, please write me that macro. Thanks in advance. Best regards. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
"Freshman" wrote:
.. I want to copy every record in columns A, B & E of every worksheet to the Summary worksheet then I use them for analysis in a pivot table. Since the records are filled in month after month, so I want the completed records of the current month can be automatically copy to the row under the last record in the Summary worksheet, i.e. May06's records under Apr06's record... If the # of sheets to be auto-summarized isn't too many .. say, data from up to 4 sheets to auto-stack into a summary sheet, then one possible formulas play to achieve it could be along the lines in this response to a similar query: http://tinyurl.com/l7kst The sample construct mentioned is available at: http://www.savefile.com/files/4567345 Dynamic record selection from multiple worksheets.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Hi Max,
Thanks for you help again. It is very nice of you. Cheers. "Max" wrote: "Freshman" wrote: .. I want to copy every record in columns A, B & E of every worksheet to the Summary worksheet then I use them for analysis in a pivot table. Since the records are filled in month after month, so I want the completed records of the current month can be automatically copy to the row under the last record in the Summary worksheet, i.e. May06's records under Apr06's record... If the # of sheets to be auto-summarized isn't too many .. say, data from up to 4 sheets to auto-stack into a summary sheet, then one possible formulas play to achieve it could be along the lines in this response to a similar query: http://tinyurl.com/l7kst The sample construct mentioned is available at: http://www.savefile.com/files/4567345 Dynamic record selection from multiple worksheets.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Hi Max,
I check your formula again and I've missed something. Please note that I've 12 primary worksheets + 1 Summary worksheet. Each primary worksheet has different number of records and records in worksheets are ranging from 80 to 120. Are the number of worksheets and records are not suitable for your formula? Please advise. Sorry to bother you again. Thanks & regards. "Max" wrote: "Freshman" wrote: .. I want to copy every record in columns A, B & E of every worksheet to the Summary worksheet then I use them for analysis in a pivot table. Since the records are filled in month after month, so I want the completed records of the current month can be automatically copy to the row under the last record in the Summary worksheet, i.e. May06's records under Apr06's record... If the # of sheets to be auto-summarized isn't too many .. say, data from up to 4 sheets to auto-stack into a summary sheet, then one possible formulas play to achieve it could be along the lines in this response to a similar query: http://tinyurl.com/l7kst The sample construct mentioned is available at: http://www.savefile.com/files/4567345 Dynamic record selection from multiple worksheets.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
"Freshman" wrote:
I check your formula again and I've missed something. Please note that I've 12 primary worksheets + 1 Summary worksheet. Each primary worksheet has different number of records and records in worksheets are ranging from 80 to 120. Are the number of worksheets and records are not suitable for your formula? Please advise. Think the # of records (80 to 120) per "primary" (ie source) sheet is not a problem. But the # of primary sheets (12) may prove to be a challenge. As it stands, the sample is structured for 4 source sheets [== 4 layers of nested IFs <g]. Let me see whether I can extend the sample to suit your specs. Give me a couple of days to ponder/work on this. I'll post back here to update you (promise!). In the interim, pl monitor your thread for other insights / programming solutions from others. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Hi Max,
Understood. Thanks for your great help anyway. Best regards. "Max" wrote: "Freshman" wrote: I check your formula again and I've missed something. Please note that I've 12 primary worksheets + 1 Summary worksheet. Each primary worksheet has different number of records and records in worksheets are ranging from 80 to 120. Are the number of worksheets and records are not suitable for your formula? Please advise. Think the # of records (80 to 120) per "primary" (ie source) sheet is not a problem. But the # of primary sheets (12) may prove to be a challenge. As it stands, the sample is structured for 4 source sheets [== 4 layers of nested IFs <g]. Let me see whether I can extend the sample to suit your specs. Give me a couple of days to ponder/work on this. I'll post back here to update you (promise!). In the interim, pl monitor your thread for other insights / programming solutions from others. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
http://www.savefile.com/files/6862951 Auto summarize n stack lines from 12 primary sheets.zip Note: To facilitate data input, the calc mode in the sample is intentionally set to manual mode. Just press F9 to recalc/update the summary upon completion / as required. The 12 source sheets (identically structured) has data assumed in cols A to E, headers in A5:E5, data from row6 to a max expected row130 (125 lines). Key col checked is col A (whether empty or not), viz.: if col A is empty, it's construed that the entire row is empty. In sheet: Summ Same labels placed in A5:E5 Using 12 empty cols to the right .. List the 12 sheetnames in R5:AC5 in the stacking sequence* that the contents are to be summarized *viz: lines from sheetname in R5, followed by lines from S5, then lines from T5, and so on (flexibility provided here). Ensure that the sheetnames in M5:P5 match exactly with the names on the sheet tabs (except for case). Watch out for any inconsistencies (extra spaces, typos, etc) In R6, copied down to R130*: =IF(INDIRECT("'"&R$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )) *assuming max expected data in any sheet is 125 lines, from row6 down In S6, copied across to AC6, filled down to AC130: =IF(INDIRECT("'"&S$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )+MAX(R$6:R$15)) Create the following defined ranges (via Insert Name Define): _1 =Summ!$R$6:$R$130 _2 =Summ!$S$6:$S$130 _3 =Summ!$T$6:$T$130 _4 =Summ!$U$6:$U$130 _5 =Summ!$V$6:$V$130 _6 =Summ!$W$6:$W$130 _7 =Summ!$X$6:$X$130 _8 =Summ!$Y$6:$Y$130 _9 =Summ!$Z$6:$Z$130 _10 =Summ!$AA$6:$AA$130 _11 =Summ!$AB$6:$AB$130 _12 =Summ!$AC$6:$AC$130 R_1 =Summ!$R$6:$AC$130 R_2 =Summ!$R$6:$AB$130 R_3 =Summ!$R$6:$AA$130 R_4 =Summ!$R$6:$Z$130 R_5 =Summ!$R$6:$Y$130 R_6 =Summ!$R$6:$X$130 R_7 =Summ!$R$6:$W$130 R_8 =Summ!$R$6:$V$130 R_9 =Summ!$R$6:$U$130 R_10 =Summ!$R$6:$T$130 R_11 =Summ!$R$6:$S$130 In A6, copied across to E6, filled down to E1505: =IF(ROW(A1)COUNT(_1),IF(ROW(A1)COUNT(R_11),IF(RO W(A1)COUNT(R_10),IF(ROW(A1)COUNT(R_9),G6, INDEX(OFFSET(INDIRECT("'"&$U$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_4,ROW(A1)-(COUNT(R_10))),_4,0)+5)), INDEX(OFFSET(INDIRECT("'"&$T$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_3,ROW(A1)-(COUNT(R_11))),_3,0)+5)), INDEX(OFFSET(INDIRECT("'"&$S$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_2,ROW(A1)-COUNT(_1)),_2,0)+5)), INDEX(OFFSET(INDIRECT("'"&$R$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_1,ROW(A1)),_1,0)+5)) In G6, copied across to K6, filled down to K1505: =IF(ROW(A1)COUNT(R_8),IF(ROW(A1)COUNT(R_7),IF(RO W(A1)COUNT(R_6),IF(ROW(A1)COUNT(R_5),L6, INDEX(OFFSET(INDIRECT("'"&$Y$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_8,ROW(A1)-(COUNT(R_6))),_8,0)+5)), INDEX(OFFSET(INDIRECT("'"&$X$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_7,ROW(A1)-(COUNT(R_7))),_7,0)+5)), INDEX(OFFSET(INDIRECT("'"&$W$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_6,ROW(A1)-(COUNT(R_8))),_6,0)+5)), INDEX(OFFSET(INDIRECT("'"&$V$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_5,ROW(A1)-(COUNT(R_9))),_5,0)+5)) In L6, copied across to P6, filled down to P1505: =IF(ROW(A1)COUNT(R_4),IF(ROW(A1)COUNT(R_3),IF(RO W(A1)COUNT(R_2),IF(ROW(A1)COUNT(R_1),"", INDEX(OFFSET(INDIRECT("'"&$AC$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_12,ROW(A1)-COUNT(R_2)),_12,0)+5)), INDEX(OFFSET(INDIRECT("'"&$AB$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_11,ROW(A1)-(COUNT(R_3))),_11,0)+5)), INDEX(OFFSET(INDIRECT("'"&$AA$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_10,ROW(A1)-(COUNT(R_4))),_10,0)+5)), INDEX(OFFSET(INDIRECT("'"&$Z$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_9,ROW(A1)-(COUNT(R_5))),_9,0)+5)) The above formula fills will cover the the aggregated/total expected range in the 12 sheets. (max 125 lines/rows expected per sheet x 12 sheets = 1500 rows) Cols A to E will return the desired summary, ie auto-stacked lines from cols A to E in the 12 source sheets where the key col A is not empty. Lines will be neatly stacked at the top in the sequence of the sheetnames listed in R5:AC5. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Sorry, scratch/disregard the response, will repost. Missed out some bits.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
ok, here's the revised sample which meets the specs <g:
http://www.savefile.com/files/6862951 Auto summarize n stack lines from 12 primary sheets.zip Note: To facilitate data input, the calc mode in the sample is intentionally set to manual mode. Just press F9 to recalc/update the summary upon completion / as required. The 12 source sheets (identically structured) has data assumed in cols A to E, headers in A5:E5, data from row6 to a max expected row130 (125 lines). Key col checked is col A (whether empty or not), viz.: if col A is empty, it's construed that the entire row is empty In sheet: Summ Same labels placed in A5:E5 Using 12 empty cols to the right .. List the 12 sheetnames in R5:AC5 in the stacking sequence* that the contents are to be summarized *viz: lines from sheetname in R5, followed by lines from S5, then lines from T5, and so on (flexibility provided here). Ensure that the sheetnames in R5:AC5 match exactly with the names on the sheet tabs (except for case). Watch out for any inconsistencies (extra spaces, typos, etc) In R6, copied down to R130*: =IF(INDIRECT("'"&R$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )) *assuming max expected data in any sheet is 125 lines, from row6 down In S6, copied across to AC6, filled down to AC130: =IF(INDIRECT("'"&S$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )+MAX(R$6:R$130)) Create the following defined ranges (via Insert Name Define): _1 =Summ!$R$6:$R$130 _2 =Summ!$S$6:$S$130 _3 =Summ!$T$6:$T$130 _4 =Summ!$U$6:$U$130 _5 =Summ!$V$6:$V$130 _6 =Summ!$W$6:$W$130 _7 =Summ!$X$6:$X$130 _8 =Summ!$Y$6:$Y$130 _9 =Summ!$Z$6:$Z$130 _10 =Summ!$AA$6:$AA$130 _11 =Summ!$AB$6:$AB$130 _12 =Summ!$AC$6:$AC$130 R_1 =Summ!$R$6:$AC$130 R_2 =Summ!$R$6:$AB$130 R_3 =Summ!$R$6:$AA$130 R_4 =Summ!$R$6:$Z$130 R_5 =Summ!$R$6:$Y$130 R_6 =Summ!$R$6:$X$130 R_7 =Summ!$R$6:$W$130 R_8 =Summ!$R$6:$V$130 R_9 =Summ!$R$6:$U$130 R_10 =Summ!$R$6:$T$130 R_11 =Summ!$R$6:$S$130 In A6, copied across to E6, filled down to E1505: =IF(ROW(A1)COUNT(_1),IF(ROW(A1)COUNT(R_11),IF(RO W(A1)COUNT(R_10),IF(ROW(A1)COUNT(R_9),G6, INDEX(OFFSET(INDIRECT("'"&$U$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_4,ROW(A1)-(COUNT(R_10))),_4,0)+5)), INDEX(OFFSET(INDIRECT("'"&$T$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_3,ROW(A1)-(COUNT(R_11))),_3,0)+5)), INDEX(OFFSET(INDIRECT("'"&$S$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_2,ROW(A1)-COUNT(_1)),_2,0)+5)), INDEX(OFFSET(INDIRECT("'"&$R$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_1,ROW(A1)),_1,0)+5)) In G6, copied across to K6, filled down to K1505: =IF(ROW(A1)COUNT(R_8),IF(ROW(A1)COUNT(R_7),IF(RO W(A1)COUNT(R_6),IF(ROW(A1)COUNT(R_5),L6, INDEX(OFFSET(INDIRECT("'"&$Y$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_8,ROW(A1)-(COUNT(R_6))),_8,0)+5)), INDEX(OFFSET(INDIRECT("'"&$X$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_7,ROW(A1)-(COUNT(R_7))),_7,0)+5)), INDEX(OFFSET(INDIRECT("'"&$W$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_6,ROW(A1)-(COUNT(R_8))),_6,0)+5)), INDEX(OFFSET(INDIRECT("'"&$V$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_5,ROW(A1)-(COUNT(R_9))),_5,0)+5)) In L6, copied across to P6, filled down to P1505: =IF(ROW(A1)COUNT(R_4),IF(ROW(A1)COUNT(R_3),IF(RO W(A1)COUNT(R_2),IF(ROW(A1)COUNT(R_1),"", INDEX(OFFSET(INDIRECT("'"&$AC$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_12,ROW(A1)-COUNT(R_2)),_12,0)+5)), INDEX(OFFSET(INDIRECT("'"&$AB$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_11,ROW(A1)-(COUNT(R_3))),_11,0)+5)), INDEX(OFFSET(INDIRECT("'"&$AA$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_10,ROW(A1)-(COUNT(R_4))),_10,0)+5)), INDEX(OFFSET(INDIRECT("'"&$Z$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_9,ROW(A1)-(COUNT(R_5))),_9,0)+5)) The above formula fills will cover the the aggregated/total expected range in the 12 sheets (max 125 lines/rows expected per sheet x 12 sheets = 1500 rows) Cols A to E will return the desired summary, ie auto-stacked lines from cols A to E in the 12 source sheets where the key col A is not empty. Lines will be neatly stacked at the top in the sequence of the sheetnames listed in R5:AC5. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote: .. Please note that I've 12 primary worksheets + 1 Summary worksheet. Each primary worksheet has different number of records and records in worksheets are ranging from 80 to 120. .. I want to copy every record in columns A, B & E of every worksheet to the Summary worksheet then I use them for analysis in a pivot table. Since the records are filled in month after month, so I want the completed records of the current month can be automatically copy to the row under the last record in the Summary worksheet, i.e. May06's records under Apr06's record... |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Since you wish to use the data in a pivot table then you could use the approach detailed in the link below. http://www.contextures.com/excelfiles.html#Pivot look for the paragragh saying.. "PT0007 - Multiple Consolidation Ranges -- Create a normal pivot table from multiple ranges, by using MS Query to join the data. One sample file contains the pivot table, the other contains the source data tables. Contains macro to update connection. PivotMultConsolPivot.zip 11 kb PivotMultConsolProv.zip 6 kb 03-Oct-05" -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=556808 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Hi Max,
Thanks for your time and your great help. Please send my best regards to your family too. Have a nice day. Cheers. Best regards. "Max" wrote: ok, here's the revised sample which meets the specs <g: http://www.savefile.com/files/6862951 Auto summarize n stack lines from 12 primary sheets.zip Note: To facilitate data input, the calc mode in the sample is intentionally set to manual mode. Just press F9 to recalc/update the summary upon completion / as required. The 12 source sheets (identically structured) has data assumed in cols A to E, headers in A5:E5, data from row6 to a max expected row130 (125 lines). Key col checked is col A (whether empty or not), viz.: if col A is empty, it's construed that the entire row is empty In sheet: Summ Same labels placed in A5:E5 Using 12 empty cols to the right .. List the 12 sheetnames in R5:AC5 in the stacking sequence* that the contents are to be summarized *viz: lines from sheetname in R5, followed by lines from S5, then lines from T5, and so on (flexibility provided here). Ensure that the sheetnames in R5:AC5 match exactly with the names on the sheet tabs (except for case). Watch out for any inconsistencies (extra spaces, typos, etc) In R6, copied down to R130*: =IF(INDIRECT("'"&R$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )) *assuming max expected data in any sheet is 125 lines, from row6 down In S6, copied across to AC6, filled down to AC130: =IF(INDIRECT("'"&S$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )+MAX(R$6:R$130)) Create the following defined ranges (via Insert Name Define): _1 =Summ!$R$6:$R$130 _2 =Summ!$S$6:$S$130 _3 =Summ!$T$6:$T$130 _4 =Summ!$U$6:$U$130 _5 =Summ!$V$6:$V$130 _6 =Summ!$W$6:$W$130 _7 =Summ!$X$6:$X$130 _8 =Summ!$Y$6:$Y$130 _9 =Summ!$Z$6:$Z$130 _10 =Summ!$AA$6:$AA$130 _11 =Summ!$AB$6:$AB$130 _12 =Summ!$AC$6:$AC$130 R_1 =Summ!$R$6:$AC$130 R_2 =Summ!$R$6:$AB$130 R_3 =Summ!$R$6:$AA$130 R_4 =Summ!$R$6:$Z$130 R_5 =Summ!$R$6:$Y$130 R_6 =Summ!$R$6:$X$130 R_7 =Summ!$R$6:$W$130 R_8 =Summ!$R$6:$V$130 R_9 =Summ!$R$6:$U$130 R_10 =Summ!$R$6:$T$130 R_11 =Summ!$R$6:$S$130 In A6, copied across to E6, filled down to E1505: =IF(ROW(A1)COUNT(_1),IF(ROW(A1)COUNT(R_11),IF(RO W(A1)COUNT(R_10),IF(ROW(A1)COUNT(R_9),G6, INDEX(OFFSET(INDIRECT("'"&$U$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_4,ROW(A1)-(COUNT(R_10))),_4,0)+5)), INDEX(OFFSET(INDIRECT("'"&$T$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_3,ROW(A1)-(COUNT(R_11))),_3,0)+5)), INDEX(OFFSET(INDIRECT("'"&$S$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_2,ROW(A1)-COUNT(_1)),_2,0)+5)), INDEX(OFFSET(INDIRECT("'"&$R$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_1,ROW(A1)),_1,0)+5)) In G6, copied across to K6, filled down to K1505: =IF(ROW(A1)COUNT(R_8),IF(ROW(A1)COUNT(R_7),IF(RO W(A1)COUNT(R_6),IF(ROW(A1)COUNT(R_5),L6, INDEX(OFFSET(INDIRECT("'"&$Y$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_8,ROW(A1)-(COUNT(R_6))),_8,0)+5)), INDEX(OFFSET(INDIRECT("'"&$X$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_7,ROW(A1)-(COUNT(R_7))),_7,0)+5)), INDEX(OFFSET(INDIRECT("'"&$W$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_6,ROW(A1)-(COUNT(R_8))),_6,0)+5)), INDEX(OFFSET(INDIRECT("'"&$V$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_5,ROW(A1)-(COUNT(R_9))),_5,0)+5)) In L6, copied across to P6, filled down to P1505: =IF(ROW(A1)COUNT(R_4),IF(ROW(A1)COUNT(R_3),IF(RO W(A1)COUNT(R_2),IF(ROW(A1)COUNT(R_1),"", INDEX(OFFSET(INDIRECT("'"&$AC$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_12,ROW(A1)-COUNT(R_2)),_12,0)+5)), INDEX(OFFSET(INDIRECT("'"&$AB$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_11,ROW(A1)-(COUNT(R_3))),_11,0)+5)), INDEX(OFFSET(INDIRECT("'"&$AA$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_10,ROW(A1)-(COUNT(R_4))),_10,0)+5)), INDEX(OFFSET(INDIRECT("'"&$Z$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_9,ROW(A1)-(COUNT(R_5))),_9,0)+5)) The above formula fills will cover the the aggregated/total expected range in the 12 sheets (max 125 lines/rows expected per sheet x 12 sheets = 1500 rows) Cols A to E will return the desired summary, ie auto-stacked lines from cols A to E in the 12 source sheets where the key col A is not empty. Lines will be neatly stacked at the top in the sequence of the sheetnames listed in R5:AC5. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote: .. Please note that I've 12 primary worksheets + 1 Summary worksheet. Each primary worksheet has different number of records and records in worksheets are ranging from 80 to 120. .. I want to copy every record in columns A, B & E of every worksheet to the Summary worksheet then I use them for analysis in a pivot table. Since the records are filled in month after month, so I want the completed records of the current month can be automatically copy to the row under the last record in the Summary worksheet, i.e. May06's records under Apr06's record... |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
Hi Steven,
Thanks for your information. I'll take a look to the files you mentioned. Have a nice day. Regards. "steven1001" wrote: Since you wish to use the data in a pivot table then you could use the approach detailed in the link below. http://www.contextures.com/excelfiles.html#Pivot look for the paragragh saying.. "PT0007 - Multiple Consolidation Ranges -- Create a normal pivot table from multiple ranges, by using MS Query to join the data. One sample file contains the pivot table, the other contains the source data tables. Contains macro to update connection. PivotMultConsolPivot.zip 11 kb PivotMultConsolProv.zip 6 kb 03-Oct-05" -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=556808 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Copying Data
You're welcome !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freshman" wrote: Hi Max, Thanks for your time and your great help. Please send my best regards to your family too. Have a nice day. Cheers. Best regards. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying data as static as source data changes | Excel Worksheet Functions | |||
| Auto updating pivot tables using external XML data | New Users to Excel | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| Copying Filtered Data | Excel Discussion (Misc queries) | |||
Linear Mode
