Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have some csv data the I pull into Excel to format and etc. The data rows
are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? |
#2
![]() |
|||
|
|||
![]()
Intuitively, everyone wants to the see the totals row at the bottom of the
sheet. As you have discovered, the simplest thing to do is to put the totals row at the top of the sheet! The first row would contain sums, the second row might contain labels and the third row on down might contain data. Doing this would allow the sum to be, for example: =SUM(A3:A65536) You just don't have to care how many "real" rows of data there are. -- Gary''s Student "Scott" wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? |
#3
![]() |
|||
|
|||
![]()
You could find the next available row (I used column A):
dim NextRow as long with activesheet nextrow = .cells(.rows.count,"A").end(xlup).row + 1 .cells(nextrow,"B").resize(1,5).formulaR1C1 _ = "=sum(r1c:r[-1]c)" end with Another option would be to insert a new row 1 and use that to hold your formulas. Then you wouldn't have to care where the data stops. You could use a formula like: =sum(B2:B65536) Scott wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? -- Dave Peterson |
#4
![]() |
|||
|
|||
![]()
Thanks for the prompt response! If I understand you correctly, this cannot be
done at the bottom of the respective page, but CAN be accomplished at each subsequent page using the "Rows to Repeat at Top" value. Is this correct? Thanks again! "Gary''s Student" wrote: Intuitively, everyone wants to the see the totals row at the bottom of the sheet. As you have discovered, the simplest thing to do is to put the totals row at the top of the sheet! The first row would contain sums, the second row might contain labels and the third row on down might contain data. Doing this would allow the sum to be, for example: =SUM(A3:A65536) You just don't have to care how many "real" rows of data there are. -- Gary''s Student "Scott" wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? |
#5
![]() |
|||
|
|||
![]()
Dave,
I guess what I'm not understanding is how to display the Totals for each page. Ultimately, what I would like to do is display the autosum at the bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the page totals. This continuing on for the continual pages =SUM(A56:A107) and A:108 being the next page totals. Maybe the VB code you referenced will handle this, I'm just not well versed with VB code and applying it to an existing spreadsheet. Scott "Dave Peterson" wrote: You could find the next available row (I used column A): dim NextRow as long with activesheet nextrow = .cells(.rows.count,"A").end(xlup).row + 1 .cells(nextrow,"B").resize(1,5).formulaR1C1 _ = "=sum(r1c:r[-1]c)" end with Another option would be to insert a new row 1 and use that to hold your formulas. Then you wouldn't have to care where the data stops. You could use a formula like: =sum(B2:B65536) Scott wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? -- Dave Peterson |
#6
![]() |
|||
|
|||
![]()
That's not what I meant. You just got suggestions to have a grand total line at
the bottom of all your data. There's nothing built into excel that does what you want (subtotals per page). Scott wrote: Dave, I guess what I'm not understanding is how to display the Totals for each page. Ultimately, what I would like to do is display the autosum at the bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the page totals. This continuing on for the continual pages =SUM(A56:A107) and A:108 being the next page totals. Maybe the VB code you referenced will handle this, I'm just not well versed with VB code and applying it to an existing spreadsheet. Scott "Dave Peterson" wrote: You could find the next available row (I used column A): dim NextRow as long with activesheet nextrow = .cells(.rows.count,"A").end(xlup).row + 1 .cells(nextrow,"B").resize(1,5).formulaR1C1 _ = "=sum(r1c:r[-1]c)" end with Another option would be to insert a new row 1 and use that to hold your formulas. Then you wouldn't have to care where the data stops. You could use a formula like: =sum(B2:B65536) Scott wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? -- Dave Peterson -- Dave Peterson |
#7
![]() |
|||
|
|||
![]()
Is there any way to build a template sheet and any csv data importted into
the worksheet is then merged into that template? Just fishing! "Dave Peterson" wrote: That's not what I meant. You just got suggestions to have a grand total line at the bottom of all your data. There's nothing built into excel that does what you want (subtotals per page). Scott wrote: Dave, I guess what I'm not understanding is how to display the Totals for each page. Ultimately, what I would like to do is display the autosum at the bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the page totals. This continuing on for the continual pages =SUM(A56:A107) and A:108 being the next page totals. Maybe the VB code you referenced will handle this, I'm just not well versed with VB code and applying it to an existing spreadsheet. Scott "Dave Peterson" wrote: You could find the next available row (I used column A): dim NextRow as long with activesheet nextrow = .cells(.rows.count,"A").end(xlup).row + 1 .cells(nextrow,"B").resize(1,5).formulaR1C1 _ = "=sum(r1c:r[-1]c)" end with Another option would be to insert a new row 1 and use that to hold your formulas. Then you wouldn't have to care where the data stops. You could use a formula like: =sum(B2:B65536) Scott wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? -- Dave Peterson -- Dave Peterson |
#8
![]() |
|||
|
|||
![]()
I guess it depends on what you mean by merge.
You could create a macro that would open your .csv file. Then you could have that same macro create a new workbook based on an existing template. Then your macro could copy all(?) the data and paste it into a worksheet in that newly created workbook. If you're trying to set up a template file that has page breaks already inserted and formulas that sum per page, you could even copy pieces of the .csv file and paste into that template worksheet nicely. But you'll want to error on the conservative side when it comes to inserting those pagebreaks into your template. If you create a template that supports 60 rows of details (and a couple of headers and a couple of "footers"), then change printers, you may find that the new printer can't fit that many rows per page and things won't look too nice--and you'll have to rearrange your data or create a nicer template and change the code that imports the .csv file. Scott wrote: Is there any way to build a template sheet and any csv data importted into the worksheet is then merged into that template? Just fishing! "Dave Peterson" wrote: That's not what I meant. You just got suggestions to have a grand total line at the bottom of all your data. There's nothing built into excel that does what you want (subtotals per page). Scott wrote: Dave, I guess what I'm not understanding is how to display the Totals for each page. Ultimately, what I would like to do is display the autosum at the bottom of each page for each column. (i.e. =SUM(A2:A54)) and A:55 being the page totals. This continuing on for the continual pages =SUM(A56:A107) and A:108 being the next page totals. Maybe the VB code you referenced will handle this, I'm just not well versed with VB code and applying it to an existing spreadsheet. Scott "Dave Peterson" wrote: You could find the next available row (I used column A): dim NextRow as long with activesheet nextrow = .cells(.rows.count,"A").end(xlup).row + 1 .cells(nextrow,"B").resize(1,5).formulaR1C1 _ = "=sum(r1c:r[-1]c)" end with Another option would be to insert a new row 1 and use that to hold your formulas. Then you wouldn't have to care where the data stops. You could use a formula like: =sum(B2:B65536) Scott wrote: I have some csv data the I pull into Excel to format and etc. The data rows are always different (from month to month). One time I will have 400 rows, the next time I might have 58. Is there a way to force Excel to Autosum defined columns on each page without having to Insert a row at the bottom of each page? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
tOTALS WON'T PRINT ON SAME PAGE | New Users to Excel | |||
how to get a cell on a page to equal the bottom most cell on dif | Excel Worksheet Functions | |||
only last cell on page to have bottom border (cell area outline) | Excel Worksheet Functions | |||
Greybar at the bottom of the page | Excel Discussion (Misc queries) | |||
how to print a row on the bottom of every page | Excel Discussion (Misc queries) |