Problem with extending list formats and formulas

[korey99]

Hey all-

I'm having a problem with extending list formats and formulas using
Excel 2000. I know there are limitations to what that feature can do,
but I thought I'd check here, since I have a very simple example that
isn't working.

I create a sheet with two columns. In the column A, I just enter any
arbitrary number in each of the first 10 cells. For column B, I want
to calculate the difference between the last two values in column A.
In other words, B2's formula is =A2-A1, and B3 = A3-A2, etc.. I copy
this formula down the first 10 cells of column B (skipping the first
row of course). The idea is pretty simple - I hope I've explained
clearly enough.

Now, when I go and type a new value in the first empty row of column
A, Excel doesn't automatically extend my formula to the new row. If
column B only references the current row (i.e B2 = A2*2). It works
fine.

Does anyone have any suggestions how to make this work?

Thanks,
Korey

 

[Niek Otten]

Hi Korey,

The only thing I can say is I experience the same behavior.
It just seems to be so! I didn't find anything in the archives newsgroup either.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hey all-
|
| I'm having a problem with extending list formats and formulas using
| Excel 2000. I know there are limitations to what that feature can do,
| but I thought I'd check here, since I have a very simple example that
| isn't working.
|
| I create a sheet with two columns. In the column A, I just enter any
| arbitrary number in each of the first 10 cells. For column B, I want
| to calculate the difference between the last two values in column A.
| In other words, B2's formula is =A2-A1, and B3 = A3-A2, etc.. I copy
| this formula down the first 10 cells of column B (skipping the first
| row of course). The idea is pretty simple - I hope I've explained
| clearly enough.
|
| Now, when I go and type a new value in the first empty row of column
| A, Excel doesn't automatically extend my formula to the new row. If
| column B only references the current row (i.e B2 = A2*2). It works
| fine.
|
| Does anyone have any suggestions how to make this work?
|
| Thanks,
| Korey
|

 

[korey99]

Thanks for the verification. The only other way I could think of
would be to apply that formula to the first 500 or so cells, but that
messes up the "last used cell" Excel calculation and causes extra
pages to be printed. Macros would probably work too, but I was trying
to come up with a simple solution without macros.

Thanks,
Korey