Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Hi everyone. :) I'm sure this is something very simple that I'm overlooking, but I can't come up with a good solution after hours of playing around. I'm hoping someone with more skill than I can help me out. :) I'm trying to create sort of an ongoing checkbook register type of sheet. I have about 8 columns per row, some columns are plain text, some numbers, and 3 or 4 of them are calculated/forumla columns based on other columns in the row. What I want to be able to do is insert a new record (row) below the existing records every so often. Catch is that I want the new rows to be formatted like all the previous rows (borders, patterns, etc) and carry the same formulas as the previous rows. I know I could just have like a thousand pre-formatted rows from the beginning that I just fill in, but I'd rather not do that because I only want Excel to print/show the neccessary amount of pages and not a bunch of blank formatted rows that I've not yet filled in. And I don't want my totals row (the row at the very end that sums up some of my columns) to be like on page 12 when my records only go up to page 3. ;) I hope that makes sense; if not, please ask and I'll try to explain better. Thanks in advance to anyone who could help me out. :) -- Namras ------------------------------------------------------------------------ Namras's Profile: http://www.excelforum.com/member.php...o&userid=29873 View this thread: http://www.excelforum.com/showthread...hreadid=495769 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Why not just copy the last formatted row and Paste/insert it below the that
row. It should maintain formatting. However, your Totals at the bottom may not update correctly. In the checkbook type worksheet that I use, I have my totals at the top instead of the bottom. That way you can total as far down as you want and not have to change the formulas each time you add a new row. When the totals are at the top, you can freeze the panes so that they'll always be visible when scrolling. Just a thought. Paul "Namras" wrote in message ... Hi everyone. :) I'm sure this is something very simple that I'm overlooking, but I can't come up with a good solution after hours of playing around. I'm hoping someone with more skill than I can help me out. :) I'm trying to create sort of an ongoing checkbook register type of sheet. I have about 8 columns per row, some columns are plain text, some numbers, and 3 or 4 of them are calculated/forumla columns based on other columns in the row. What I want to be able to do is insert a new record (row) below the existing records every so often. Catch is that I want the new rows to be formatted like all the previous rows (borders, patterns, etc) and carry the same formulas as the previous rows. I know I could just have like a thousand pre-formatted rows from the beginning that I just fill in, but I'd rather not do that because I only want Excel to print/show the neccessary amount of pages and not a bunch of blank formatted rows that I've not yet filled in. And I don't want my totals row (the row at the very end that sums up some of my columns) to be like on page 12 when my records only go up to page 3. ;) I hope that makes sense; if not, please ask and I'll try to explain better. Thanks in advance to anyone who could help me out. :) -- Namras ------------------------------------------------------------------------ Namras's Profile: http://www.excelforum.com/member.php...o&userid=29873 View this thread: http://www.excelforum.com/showthread...hreadid=495769 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Visit
http://support.microsoft.com/?kbid=231002 and follow the instructions. Borders don't work as well as I would like, but in general, the technique can be helpful - I don't rely on it, usually. Also, put your sums on top, so that they don't need to by moved down. Soemthing like =SUM(A2:A1000)... HTH, Bernie MS Excel MVP "Namras" wrote in message ... Hi everyone. :) I'm sure this is something very simple that I'm overlooking, but I can't come up with a good solution after hours of playing around. I'm hoping someone with more skill than I can help me out. :) I'm trying to create sort of an ongoing checkbook register type of sheet. I have about 8 columns per row, some columns are plain text, some numbers, and 3 or 4 of them are calculated/forumla columns based on other columns in the row. What I want to be able to do is insert a new record (row) below the existing records every so often. Catch is that I want the new rows to be formatted like all the previous rows (borders, patterns, etc) and carry the same formulas as the previous rows. I know I could just have like a thousand pre-formatted rows from the beginning that I just fill in, but I'd rather not do that because I only want Excel to print/show the neccessary amount of pages and not a bunch of blank formatted rows that I've not yet filled in. And I don't want my totals row (the row at the very end that sums up some of my columns) to be like on page 12 when my records only go up to page 3. ;) I hope that makes sense; if not, please ask and I'll try to explain better. Thanks in advance to anyone who could help me out. :) -- Namras ------------------------------------------------------------------------ Namras's Profile: http://www.excelforum.com/member.php...o&userid=29873 View this thread: http://www.excelforum.com/showthread...hreadid=495769 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Thanks for the reply. :) If it were for me, that is probably what I would do, but I'm creating this for people not too fluent with Excel, so it needs to be pretty straightforward. The ideal way I can think of would be to have a single empty formatted row (with the neccessary formulas) always below the last entry. Sort of like a "New Entry" row. As soon as the user types something in this row, Excel creates a new empty formatted row below it, and this goes on and on. What I can't figure out is how to get Excel to automatically create these new formatted ("New Entry") rows automatically. Heh, it's really hard to explain. :p -- Namras ------------------------------------------------------------------------ Namras's Profile: http://www.excelforum.com/member.php...o&userid=29873 View this thread: http://www.excelforum.com/showthread...hreadid=495769 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Namras
Method 1 With Excel 2002 and 2003 you will find the "extend data range formats and formulas" under ToolsOptionsEdit. Make sure that is checked. Assuming A11 has a formula =SUM(A1:A10) Insert a new row above A11 and enter a new number. The format and formulas will be extended to cover this. Method 2 Have your totals formulas in a row near the top and have them refer to the entire column. In F1 enter =SUM(A:A) There are other methods of hiding the the unused rows with formulas from printing. Leave them looking blank with an IF statement =IF(B1="","",A1*B1) Build and name a Dynamic Range for your Print Range. Gord Dibben Excel MVP On Fri, 23 Dec 2005 09:43:55 -0600, Namras wrote: Hi everyone. :) I'm sure this is something very simple that I'm overlooking, but I can't come up with a good solution after hours of playing around. I'm hoping someone with more skill than I can help me out. :) I'm trying to create sort of an ongoing checkbook register type of sheet. I have about 8 columns per row, some columns are plain text, some numbers, and 3 or 4 of them are calculated/forumla columns based on other columns in the row. What I want to be able to do is insert a new record (row) below the existing records every so often. Catch is that I want the new rows to be formatted like all the previous rows (borders, patterns, etc) and carry the same formulas as the previous rows. I know I could just have like a thousand pre-formatted rows from the beginning that I just fill in, but I'd rather not do that because I only want Excel to print/show the neccessary amount of pages and not a bunch of blank formatted rows that I've not yet filled in. And I don't want my totals row (the row at the very end that sums up some of my columns) to be like on page 12 when my records only go up to page 3. ;) I hope that makes sense; if not, please ask and I'll try to explain better. Thanks in advance to anyone who could help me out. :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Gord Dibben Wrote: Method 2 Have your totals formulas in a row near the top and have them refer to the entire column. In F1 enter =SUM(A:A) There are other methods of hiding the the unused rows with formulas from printing. Leave them looking blank with an IF statement =IF(B1="","",A1*B1) Build and name a Dynamic Range for your Print Range. Ah yes... this is something like I'm looking for. The only thing I don't understand is the dynamic print range. I've copied my formulas down 500 rows using the IF statements so the cells are totally blank until data is inserted. Excel still wants to print all 500 empty rows though. How can I make it print only rows that have something in it? Thanks in advance. :) -- Namras ------------------------------------------------------------------------ Namras's Profile: http://www.excelforum.com/member.php...o&userid=29873 View this thread: http://www.excelforum.com/showthread...hreadid=495769 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate rows forever...
Nevermind, I think I figured it out. Thanks so much for all your help guys! :) -- Namras ------------------------------------------------------------------------ Namras's Profile: http://www.excelforum.com/member.php...o&userid=29873 View this thread: http://www.excelforum.com/showthread...hreadid=495769 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting All Rows for Duplicate Entries Except Those With Most Items In Row | Excel Discussion (Misc queries) | |||
REALLY miussing rows in Excel2000 | Excel Discussion (Misc queries) | |||
Keeping duplicate rows | Excel Worksheet Functions | |||
Find duplicate rows and add together | Excel Discussion (Misc queries) | |||
Showing Duplicate rows in excel | Excel Discussion (Misc queries) |