Automatically replicating a row as I enter data?

Is it possible to create a single row in an Excel spreadsheet that will
automatically insert a new row, duplicating the formatting and formulas from
the current row once data has been added?

i.e.

Row 1 and 2 have titles
Row 3 has a Qty, Description and Price and final cost formula (Qty * Price)
Row 4 shows the sum of column 4 (the final cost)

If I enter data into row 3, I want row 4 to automatically move to row 5 and
then row 4 to contain the same formatting and formulas as row 3 (with no
data).

Thanks!


0
dont.spam (33)
8/8/2004 2:51:18 PM
excel 39879 articles. 2 followers. Follow

4 Replies
522 Views

Similar Articles

[PageSpeed] 46

Noozer,

First, please post only to one group.

With Excel 2002, you can use Tools - Options - Edit - Extend list formats
and formulas.  There are some conditions that there must be at least 5 rows
already there, and the formulas and formats must appear in at least three of
them.  Or something like that.  I can't find it again in Help, so I think
I'll go outside and enjoy the nice weather.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Noozer" <dont.spam@me.here> wrote in message
news:G5rRc.51602$gE.38148@pd7tw3no...
> Is it possible to create a single row in an Excel spreadsheet that will
> automatically insert a new row, duplicating the formatting and formulas
from
> the current row once data has been added?
>
> i.e.
>
> Row 1 and 2 have titles
> Row 3 has a Qty, Description and Price and final cost formula (Qty *
Price)
> Row 4 shows the sum of column 4 (the final cost)
>
> If I enter data into row 3, I want row 4 to automatically move to row 5
and
> then row 4 to contain the same formatting and formulas as row 3 (with no
> data).
>
> Thanks!
>
>


0
nowhere1083 (630)
8/8/2004 3:45:58 PM
"Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:%23dC3L7VfEHA.3964@TK2MSFTNGP12.phx.gbl...
> Noozer,
>
> First, please post only to one group.

Multiposting is bad form and both groups were relevant. Trim the groups to
the one you're replying to (as you did) and I'll still see the reply.

> With Excel 2002, you can use Tools - Options - Edit - Extend list formats
> and formulas.  There are some conditions that there must be at least 5
rows
> already there, and the formulas and formats must appear in at least three
of
> them.  Or something like that.  I can't find it again in Help, so I think
> I'll go outside and enjoy the nice weather.

Thanks for the info... Definately gives me a place to start.

> > Is it possible to create a single row in an Excel spreadsheet that will
> > automatically insert a new row, duplicating the formatting and formulas
> from
> > the current row once data has been added?
> >
> > i.e.
> >
> > Row 1 and 2 have titles
> > Row 3 has a Qty, Description and Price and final cost formula (Qty *
> Price)
> > Row 4 shows the sum of column 4 (the final cost)
> >
> > If I enter data into row 3, I want row 4 to automatically move to row 5
> and
> > then row 4 to contain the same formatting and formulas as row 3 (with no
> > data).


0
dont.spam (33)
8/8/2004 5:38:39 PM
Noozer,

The reason that multiposting is discouraged is that it can lead to multiple
responses in the groups where responders aren't aware that it might have
been answered elsewhere.  Thus their time is wasted.

Good luck with the Extend List Format option.  Another, and more robust,
solution is to have a macro build a new row each time one is needed.  It can
be an event macro, to make it automatic.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Noozer" <dont.spam@me.here> wrote in message
news:zytRc.51982$J06.49719@pd7tw2no...
>
> "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> news:%23dC3L7VfEHA.3964@TK2MSFTNGP12.phx.gbl...
> > Noozer,
> >
> > First, please post only to one group.
>
> Multiposting is bad form and both groups were relevant. Trim the groups to
> the one you're replying to (as you did) and I'll still see the reply.
>
> > With Excel 2002, you can use Tools - Options - Edit - Extend list
formats
> > and formulas.  There are some conditions that there must be at least 5
> rows
> > already there, and the formulas and formats must appear in at least
three
> of
> > them.  Or something like that.  I can't find it again in Help, so I
think
> > I'll go outside and enjoy the nice weather.
>
> Thanks for the info... Definately gives me a place to start.
>
> > > Is it possible to create a single row in an Excel spreadsheet that
will
> > > automatically insert a new row, duplicating the formatting and
formulas
> > from
> > > the current row once data has been added?
> > >
> > > i.e.
> > >
> > > Row 1 and 2 have titles
> > > Row 3 has a Qty, Description and Price and final cost formula (Qty *
> > Price)
> > > Row 4 shows the sum of column 4 (the final cost)
> > >
> > > If I enter data into row 3, I want row 4 to automatically move to row
5
> > and
> > > then row 4 to contain the same formatting and formulas as row 3 (with
no
> > > data).
>
>


0
nowhere1083 (630)
8/8/2004 6:11:06 PM
Yeah but Noozer actually crossposted to both groups at the same time.

Although, I don't believe that even that was necessary (either group would be
fine), crossposting is less of an egregious sin than multiposting <vbg>.

Earl Kiosterud wrote:
> 
> Noozer,
> 
> The reason that multiposting is discouraged is that it can lead to multiple
> responses in the groups where responders aren't aware that it might have
> been answered elsewhere.  Thus their time is wasted.
> 
> Good luck with the Extend List Format option.  Another, and more robust,
> solution is to have a macro build a new row each time one is needed.  It can
> be an event macro, to make it automatic.
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
> 
> "Noozer" <dont.spam@me.here> wrote in message
> news:zytRc.51982$J06.49719@pd7tw2no...
> >
> > "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> > news:%23dC3L7VfEHA.3964@TK2MSFTNGP12.phx.gbl...
> > > Noozer,
> > >
> > > First, please post only to one group.
> >
> > Multiposting is bad form and both groups were relevant. Trim the groups to
> > the one you're replying to (as you did) and I'll still see the reply.
> >
> > > With Excel 2002, you can use Tools - Options - Edit - Extend list
> formats
> > > and formulas.  There are some conditions that there must be at least 5
> > rows
> > > already there, and the formulas and formats must appear in at least
> three
> > of
> > > them.  Or something like that.  I can't find it again in Help, so I
> think
> > > I'll go outside and enjoy the nice weather.
> >
> > Thanks for the info... Definately gives me a place to start.
> >
> > > > Is it possible to create a single row in an Excel spreadsheet that
> will
> > > > automatically insert a new row, duplicating the formatting and
> formulas
> > > from
> > > > the current row once data has been added?
> > > >
> > > > i.e.
> > > >
> > > > Row 1 and 2 have titles
> > > > Row 3 has a Qty, Description and Price and final cost formula (Qty *
> > > Price)
> > > > Row 4 shows the sum of column 4 (the final cost)
> > > >
> > > > If I enter data into row 3, I want row 4 to automatically move to row
> 5
> > > and
> > > > then row 4 to contain the same formatting and formulas as row 3 (with
> no
> > > > data).
> >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/8/2004 10:25:55 PM
Reply:

Similar Artilces:

Last auto-generated replication-id
Hi, In an MS Access table I have a one-field primary key, that is an auto- increment field of type Replication-id. After inserting a single record using SQL INSERT, I want to obtain the key of the new record. I have done this before with auto-increment fields of type Numeric and then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work for replication id's. So how do I find my new record? Peter. Peter Laman <p.n.laman@scarlet.be> wrote in news:1178262329.725006.29060@y5g2000hsa.googlegroups.com: > In an MS Access table I have a one-field primary key...

How do I keep hidden columns and rows hidden when copying and pri.
I am trying to copy data from a spreadsheet to another and then merging it to lables, but the hidden columns and rows become unhidden. I have this problem when trying to print the spreadsheet, also. Have you tried grouping the columns? Mark each set of columns and do: Data; Group and Outline; Group; Columns This way the columns will remain grouped when you copy or prin -- gatwickx ----------------------------------------------------------------------- gatwickxx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1561 View this thread: http://www.excelforum.com...

A program is trying to automatically send e-mail on your behalf.
I tried posting this in microsoft.public.outlook.general, but no nibbles. ------------------------------------------------------------------------------------------------------ Does anybody know how to turn the "A program is trying to automatically send e-mail on your behalf." prompte off? It pops when one of my MS Access apps is emailing one of it's reports to somebody. It happens on the Citrix server that one of my MS Apps is running on, but it does not happen when the app is running on my own PC. Consequently I'm hoping for some user-configurable setting that controls...

remove 1st 2 letters in each row
I have 30 rows and each row I need to delete the first 3 letters. ex UNE127 UNE50 so it will be 127 50 Can someone please tell me how I can do this without going in and deleting each row. Thanks A simple MID might suffice Assume your data in A2 down In B2: =MID(A2,4,99)+0 Copy down. The "+0" bit will convert the extracted text nums to real numbers. Success? hit the YES below -- Max Singapore --- "Cathy" wrote: > I have 30 rows and each row I need to delete the first 3 letters. > ex > UNE127 > UNE50 > &...

Automatic CC
Hello, Is there any way to create a template where a contact is automatically CC in the email. So I dont forget to cc them every time I send an email. Thanks ...

Money 2006 constantly faults with automatic updates
Whenever I use the Internet automatic updates, money faults out and wants to send a report to Microsoft. Also my account list shows most accounts with a status of 'Update In Progress' I've done a complete reinstall after removing it and power cycling my machine. I thought it may have been something with removing 2004 and installing 2006, but got same problem Has anyone else been having this problem? I really just want to go back to Money 2004. What's the error message? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq f...

combining data from 2 sheets
Hi.. I have data in 2 sheets which i want it to be displayed in the third sheet. Please suggest me how to proceed with this... ------**------ Example : Sheet 1: Sheet 2 : No Name1 No Name2 1 abc 1 efg 2 eds 2 xyz 4 def 3 aaa 5 dss 6 ddd The Result should be : Sheet 3 : No Name1 Name2 1 abc efg 2 eds xyz 3 aaa 4 def ...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

Automatic updates, automatically disabling itself
Automatic updates, automatically disabling itself. 2nd day now onecare popped up to tell me the updates turned themselves = off. I'm thinking what malware would want to turn the updates off ? Any idea what's going on here ? Did I AUTOMATICALLY install an update = that caused this ? <keepout@yahoo.com.invalid> wrote in message news:8l7mi5tkgp1gd15fkef71ll5mrui9ukmht@4ax.com... > Automatic updates, automatically disabling itself. > > 2nd day now onecare popped up to tell me the updates turned themselves > off. Drop OneCare and install Microsoft Sec...

Column format to override row fomat in FrX
In FrX the row format overrides the column format for font styles eg. bold etc. it would be useful if it was possible to have the option for the column format to override the row fomat ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesss...

Purchase Orders
Hello, I'm trying to create POs using GP (version 8.00g34) on our GP server, however going Transactions -> Purchasing -> Purchase Order Entry generates a "Cannot access this form because the dictionary containing it is not loaded." error. The other areas of GP (which I have access to) look OK. Could there be something wrong with my Dynamics.set file? We have workstations that use GP and the .set file looks the same. Any advice would be greatly appreciated. Thanks in advance, Chris Check to make sure there security is not looking for an alternate Great Plains windo...

I open the MDB, the form runs automatically
Simple question I have an MDB file with 20 files and 7 forms in it. When I open it up, the form runs immediately an access goes into some sort of protected mode. I can't view the tables, queries etc. I can see the code by pressing Alt F11 but not the data. I created a blank db and imported the mdb so I could see the tables. But how do I open the original without it running the code? When I look at the code. I don't see anything that sets properties or invokes the code automatically. There are no macros in this, just the forms and associated code with them. Any ideas Thanks Colm ...

Find highest value in each row.
Hi, I have a problem trying to find the highest value for each row. I have a query, which adds up scores. Its based on a table something like this orgID Score1 Score2 Score3 Score4 Score5 Score6 1 10 1 5 2 11 20 2 1 4 2 12 11 24 1 10 7 15 12 1 2 and so on..... I have query that basicly adds up some of the scores into groups. So its like: orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+ [Score6] 1 16 ...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

My worksheet automatically adds a fill color when I enter text.
When I type text in a blank cell, the cell automatically is highlightes with the bright yellow fill. There is no conditional formatting on this workbook. Check your VBA editor. You might have an Event Macro which does what, usually, a CF does. Try to Increase the Macro Security to its maximum level in order to eliminate the Event Macro from running... You may, also consider to "rem" all the Macro commands by adding a Preceding apostrophe to each command. Micky > When I type text in a blank cell, the cell automatically is highlightes with > the bright yello...

Access 2 Excel
I exported an annual worklist from an Access table to Excel. There is column for tasks, one for frequency (TD, D, TW, W, TM, M, Q, SA, A twice daily, daily, twice monthly, etc) and a column for each month. The month columns have the same coded data as the frequency colum depending on when the task is done. The problem is that I tried to make a pivot table to count how man tasks (by frequency type) are done each month. Excel is counting th empty cells as if they had data. Search and replace doesn't work. even tried replacing the blank data in Access with ~ and re-exportin it. Ex...

Simpler way to suppress missing data?
I created a sheet 8-10 cells where the user can fill in values. There are 20-25 cells containing the results of calculations based on those input cells. Initially, the input cells are blank. This causes various results in the derivative cells. Some of them get Value errors, some get division by zero errors, and others get zero. I want any cell that depends other cells to show a blank until all source cells have data. The best way I could find to do this is to replace a formula like =(B2*C2)+(D2*E2) with this monstrosity =IF(OR(B2="",C2="",D2="",E2="...

Use a cell's value as row number for another cell in Excel
Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the reference for the row value for a different series of cells (D2:F4). For example: Column A B C D E F Row 1 1000 30 170 1 2 3 2 1500 60 190 3 1200 20 175 I want to get the following cells to equal: D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1); E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2); F2=1200 (In other words: F2 = A3); F3=20 ...

how to enter a value, and have it SUMMED with the existing cell co
An Excel cell contains, say= 4. How do you enter, say=2, and have the result = 6? (not COPYING and Paste As ...) ...

Pinning a row
How do I keep the first row always visible when I scroll down? Click in A2, and then, <Window> <Freeze Panes> Here's an old post that might help explain: http://tinyurl.com/2qdzt -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick" <rhuldisch@hotmail.com> wrote in message news:93E01890-F83F-40CC-9F6A-F2D44EEF3D36@microsoft.com... > How do I keep the first row alway...

Having numbers automatically appear bold
Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above $30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

Inserting Rows Problem
Hi there, I have a list of about 2000 rows. However, in between each of thes rows I want to insert a blank row that can be used for people to writ in once the list is printed off. Is there a quick way to do this? Kind regards Andre -- andyp16 ----------------------------------------------------------------------- andyp161's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1165 View this thread: http://www.excelforum.com/showthread.php?threadid=26611 If you insert rows it will affect sorting, etc. and be a real pain. Many posts here want to delete blank rows ...

3rd party tool for replicating Outlook Calendar
Can anyone recommend a 3rd party tool for replicating Outlook 2007 or 2010 calendar items into sharepoint 2007 or 2010? I know you can link Outlook and SharePoint together and can drag calendar items around but I want a tool that will automaticaly synch new and updated Outlook calendar items to my shared team calendar in sharepoint. Thanks, Ken Im not too familiar with sharepoint, however i do know of a 3rd party MS Office addin that replicates Outlook Calendars as MS Word and Excel format... This hay help achieve your needs: http://www.wincalendar.com/Outlook-Calenda...

Public Folder Replication #32
Hi there, we've got 2 Exchange 2K3 Std edition and 2 DC 2K3 in 2 different sites under 1 Domain. All users, Global groups, their mailboxes and departmental public folders have been created on the servers depending of where they are based at. My question is I'm trying to set "Directors" Global Group and their users to able to access all public folders on both sides. At the moment it seems like only mails replicate between 2 sites. But public folders can not be seen on an exchange server if it's been created on the other site. Is there any reason why public folders don...