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

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

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

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. :confused:

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

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. :)

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

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