Adding a row to a table (again)

[LurfysMa]

There has to be a way to extend an array in Excel and have all of the
references update correctly.

I have a spreadsheet for tracking mileage on my car. The heart of the
spreadsheet is an array (table) of mileage readings at irregular
intervals. Here is some sample data showing row and column numbers.

B C D E
Date Odom Miles Days
17 6/13/05 51,114 2,956 (=C17-C16) 294 (=B17-B16)
18 6/14/05 51,132 18 (=C18-C17) 1 (=B18-B17)
19 3/23/06 54,169 3,037 (=C19-C18) 282 (=B19-B18)
20 3/12/07 58,255 4,086 (=C20-C19) 354 (=B20-B19)

This array, then, is B1320.

The spreadsheet also contains lots of other calculations based on this
array including several match() and index() functions. Many of these
depend on trhe "last" entry on the array (currently B20).

=INDEX(B17:B20,Q23,COLUMN(B20)

I would like a method that I can use to add a row (21) to the array
and have:

1. The new row acquire all of the attributes of the other rows, and
2. All references the last row (20) be updated to the enw last row
(21).

Here's what I've tried that doesn't work:


Drag-copy last row down.
* Select the last row (20)
* Ctrl-shift-drag the top border to the bottom.
This works perfectly for adding the new row. It acquires all fo the
attributes of the old last row. All I need to do is enter the new date
and odometer reading and everything works.
- However, references to "B20" from outside the array do not get
updated.


Drag-copy & insert last row up. This fails. When I try to Ctrl-shift
drag the bottom border to the top, nothing happens.


Insert a row below the last row.
* Select the row after the last row (21)
* Right click
* Select Insert
This adds a row, which inherits the cell formatting, but not the
formulas. It also does not affect references to B20.


Insert a row between last 2 rows.
* Select last row (20)
* Right click
* Select Insert
This almost works. It inserts a new row 20 and shoves 20 to 21. This
gets the references to B20 updated to B21. Unfortunately, the formulas
in rows 20 and 21 get messed up. Those that used to read =B20-B21 now
read =B20-B18 -- a 2-row difference. I can fix that by copying the row
above and updating all the formulas, but that should not be necessary.


Paste-insert row 20 after row 20.
* Select the last row (20)
* Right click
* Select Insert
This almost works, too. I get a new row that looks just like row 20
except that the formulas are off again. A similar thing happens if I
paste-insert between the last 2 rows.


Help. This ought to be easy. Surely people need to do this all the
time.

 

[Roger Govier]

Hi

Take a look at Data>List>Create List
followed by Data>List>Total Row
This will automatically extend your list for you adding in the
appropriate formulae

 

[LurfysMa]

Hi

Take a look at Data>List>Create List
followed by Data>List>Total Row
This will automatically extend your list for you adding in the
appropriate formulae

I'm running Excel 2000. There is no "List" option on the "Data" menu
item. Is this in a later release?

I do have a "Form" option, which seems to do some of the things you
mentioned. I was able to get Excel to recognize my table and bring up
a "new record" input box, but when it added the record, it was truly a
new record. Since the previous old record didn't move, references
(outside the list) to that row were updated.

 

[LurfysMa]

There has to be a way to extend an array in Excel and have all of the
references update correctly.

I have a spreadsheet for tracking mileage on my car. The heart of the
spreadsheet is an array (table) of mileage readings at irregular
intervals. Here is some sample data showing row and column numbers.

B C D E
Date Odom Miles Days
17 6/13/05 51,114 2,956 (=C17-C16) 294 (=B17-B16)
18 6/14/05 51,132 18 (=C18-C17) 1 (=B18-B17)
19 3/23/06 54,169 3,037 (=C19-C18) 282 (=B19-B18)
20 3/12/07 58,255 4,086 (=C20-C19) 354 (=B20-B19)

This array, then, is B1320.

The spreadsheet also contains lots of other calculations based on this
array including several match() and index() functions. Many of these
depend on trhe "last" entry on the array (currently B20).

=INDEX(B17:B20,Q23,COLUMN(B20)

I would like a method that I can use to add a row (21) to the array
and have:

1. The new row acquire all of the attributes of the other rows, and
2. All references the last row (20) be updated to the enw last row
(21).

I have come up with a solution that works. It involves replacing the
second term in difference formula (=B20-B19) with a more complicated
formula that always selects the cell that is 1 row above:

=B20-INDIRECT(ADDRESS(ROW(B20)-1,COLUMN(B20),4))

This is a bit more complicated that I had hoped for, but it works,
provided that when I add a row, I add it above the last row so that
the last row gets moved. This will take care of references to the last
row getting updated and the formula above keeps the internal
differences correct.

To make this work, I am using the following procedure for adding a new
row:

* Select the current last row.
* Ctrl-C to start copy operation.
* Right click on last row (currently selected).
* Select Insert Copied Cells. This will insert a new row above the
current last row that is an exact duplicate of the last row. This
essentially moved the last row up.
* Press Escape to kill the copy function.
* Fill in the new data for the new (added) row.

This is less convenient and straightforward than a simple "add a row"
function, but it's the simplest I could come up with and it works.

If I make a mistake and need to delete a row, I need to be careful.
Simply deleting the last row will not work. The simplest procedure I
could come up with is to copy the next-to-last row to the last row,
then delete the next to last row:

* Select next-to-last row
* Ctrl-C to start copy operation
* Select last row
* Ctrl-V to paste the next-to-last row onto the last row
* Select the next-to-last row
* Right-click on next-to-last row
* Select delete

I'm not thrilled with this solution, but I am happy. However, if
anyone has a better solution, I would be interested in hearing about
it.

 

[LurfysMa]

I have come up with a solution that works. It involves replacing the
second term in difference formula (=B20-B19) with a more complicated
formula that always selects the cell that is 1 row above:

=B20-INDIRECT(ADDRESS(ROW(B20)-1,COLUMN(B20),4))

I just discovered an even better solution involving Offset():

=B20-OFFSET(B20,-1,0)

Much cleaner and more readable.

Any better offers? ;-)

 

[Roger Govier]

Sorry, misread the SP3 as 2003.
Lists were added from XL2002 onward.

 

[David McRitchie]

"LurfysMa"

I just discovered an even better solution involving Offset():

=B20-OFFSET(B20,-1,0)

Good you found your own solution, so you probably don't
need the first link below, but might be interested in the second.

see
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm