Who created this monster!

[Robert Murrell]

I knew how to do this the last time I used Excel last year.

I have Excel 2000. I have a worksheet with a number of columns. Row 1
contains column headings. Row 2 contains data. Most of the cells are text.
Some are numbers. A few have formulas, like =(C2*10) + (D2*5).
Row 3 contains some column totals and is always meant to be the last row.
Now I have entered all the data in row 2 and at the last column of my data.
How do I open a new row following the row I just entered maintaining the
formulas but automatically adjusting the references? This would be a new
row 3. The totals row would now be row 4. Underneath the column that has
the formula =(C2*10) + (D2*5), there should be a new formula
=(C3*10)+(D2*5).

Using the Insert Row menu command, a new blank row is inserted, but none of
the formulas are replicated. And copy/pasting the formula from the previous
row doesn't change the cell references. I would expect entering Ctrl-Enter
at the last column would do what I want, like adding a new row in a Word
table. Why is something so fundamental to a spreadsheet so hard to do? Why
is that annoying animated graphic that is supposed to help so useless? Why
are there no tutorials with this product?

Remove '-dummy' to reply, or just post here.

 

[Scott Simpson]

You probably have absolute reference turned on. The way to check this is to
look in your formulas and if they have a '$' before the column and row
references, you do. Here's a brief explanation from the excel help section:
Relative vs. absolute references Depending on the task you want to perform
in Excel, you can use either relative cell references, which are references
to cells relative to the position of the formula, or absolute references,
which are cell references that always refer to cells in a specific location.
If a dollar sign precedes the letter and/or number, such as $A$1, the column
and/or row reference is absolute. Relative references automatically adjust
when you copy them, and absolute references don't. Look in excel help on
how to change the setting.

 

[Robert Murrell]

Will relative references automatically be added to cells that have formulas
when I insert a new row? I can copy and paste a row and the absolute
references will automatically adjust. So I have a blank template line that
I can paste in. However, I have to insert a row, copy the template, paste
it in the new row, and fill in the cells. This seems insane to me. I just
want to add a new line and fill it out.

 

[RWN]

Not sure what you're getting at, but;
Inserting a row will not automatically bring down any contents of the
prior row (and, believe it or not, you wouldn't want it to!).

 

[Robert Murrell]

I want to be in data entry mode. Enter a line of data, hit return, enter a
line of data, hit return. I don't want to enter the formulas each line. I
want the values in the numeric cells to be processed in a different cell,
and then I want that column totalled in the last row. It seems like a
simple, fundamental thing that any spreadsheet should do.

 

[Jonathan Rynd]

I want to be in data entry mode. Enter a line of data, hit return,
enter a line of data, hit return.

Sounds like you want a database rather than a spreadsheet. Databases can
have calculated columns.

Two ways I see to do this in Excel:
1. Use a macro that automatically copies down the formulas to that line.
2. Copy down the formulas yourself as far as they need to go.

 

[Earl Kiosterud]

Robert,

You may be able to use "Extend formats and formulas" in Tools - Options -
Edit. It's in XL2002, but I don't know about 2000. You have to have at
least 5 rows before it works. (In the meantime, just manually copy the
formulas with the fill handle, or edge-drag them with Ctrl held (copy
instead of move)). It won't copy the formula when you insert the row
between your last record and the total row, but when you've filled in the
new row, it will.

Another option is to not have totals at the bottom. Put them at the top
above your headings. If you freeze (Window - Freeze panes), they'll always
be visible. Totals at the bottom are left over from manual sheets, though
your users may balk. This removes the necessity of inserting a row each
time, though you still need to get the formulas copied..

Copy/pasting the formulas should definitely change the cell references,
unless they're absolute (=$C$2*10) + ($D$2*5). This is regardless of how
you copied them: Copy/Paste, Edge-drag with Ctrl held, or the Fill Handle.

Or a macro could do all the work.

 

[David McRitchie]

Don't know why I don't see the original, but I do see a copy of it.
Take a look at
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
Look at the first macro and also make changes to your formulas
to use OFFSET.

When the formula refers to row above and the same row you
can use OFFSET, which will make inserting/deleting rows
a lot easier since you won't have to correct formulas. Also applies
to totals at the bottom of columns.

Excel 2000 does have "Extend formats and formulas"

 

[Norman Harker]

Hi David!

Re: "Don't know why I don't see the original"

Original question was posted 14-Jan-2004 with replies all within 1
day. I suspect you clear out the old posts every now and again.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.