Table no longer copies formulas to new rows

[Stan Brown]

It's 32-bit Excel 2010.

I have a table of date, deposit, withdrawal, balance, and some other
columns. Previously if I entered a date in the next row, the
formulas automatically propagated themselves to hat new row; now hey
don't. I don't know what I did to cause that change, but I'd be
grateful for guidance.

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

 

[CellShocked]

It's 32-bit Excel 2010.

I have a table of date, deposit, withdrawal, balance, and some other
columns. Previously if I entered a date in the next row, the
formulas automatically propagated themselves to hat new row; now hey
don't. I don't know what I did to cause that change, but I'd be
grateful for guidance.

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

Did you start with a blank template?

If so, you could re-open the original, and insert your data except for
the last couple rows (since you do not know where the error is), then try
the new row add move you made and see if they copy.

If so, you would then re-do any missing rows one at a time, being sure
to observe continued proper behavior.

A glance at certain particulars between the two populated tables would
bring the broken table's error to light.

I would simply continue on with the reconstructed file.

So, if the orig. template is available, see if it has a limit at the
number of records you are at, or simply use it and try to insert the
data, if needed, or if not needed, enter only the data which you do need.

Though I am sure someone here is familiar with this auto-gen function
you refer to when you type in a new date on the row below your last (is
that how I understand it?).

You will likely get far better help than what I just gave you.

 

[Stan Brown]

[quoted text muted]

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

Did you start with a blank template?

If so, you could re-open the original, and insert your data except for
the last couple rows (since you do not know where the error is), then try
the new row add move you made and see if they copy.

Thanks for replying.

No, I didn't use any kind of template. I actually created this table
in Excel 2003 from an ordinary rectangular range, but I've been using
it successfully for months under Excel 2010. There are thousands of
lines, so I dread the thought of starting over.

 

[Gord Dibben]

Stan

In 2003 there is the Tools>Options>Edit>Extend data range

formats and formulas

Something similar in 2010?


Gord Dibben MS Excel MVP

[quoted text muted]

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

Did you start with a blank template?

If so, you could re-open the original, and insert your data except for
the last couple rows (since you do not know where the error is), then try
the new row add move you made and see if they copy.

Thanks for replying.

No, I didn't use any kind of template. I actually created this table
in Excel 2003 from an ordinary rectangular range, but I've been using
it successfully for months under Excel 2010. There are thousands of
lines, so I dread the thought of starting over.

 

[CellShocked]

[quoted text muted]

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

Did you start with a blank template?

If so, you could re-open the original, and insert your data except for
the last couple rows (since you do not know where the error is), then try
the new row add move you made and see if they copy.

Thanks for replying.

No, I didn't use any kind of template. I actually created this table
in Excel 2003 from an ordinary rectangular range, but I've been using
it successfully for months under Excel 2010. There are thousands of
lines, so I dread the thought of starting over.

Using a copy of the file, you could experiment with defining the table
array again.

Again, I am sure someone will provide you with the *right* answer.

My experience is too limited, and I only thought of the template thing
because it sounded like you were unfamiliar with the construct that
created your table.

Someone else will fix you up soon, I am sure.

 

[Stan Brown]

On Tue, 31 May 2011 20:56:59 -0400, Stan Brown

[quoted text muted]

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

In 2003 there is the Tools>Options>Edit>Extend data range

formats and formulas

Something similar in 2010?

Thanks, Gord, but no. There's a "resize table" button, but when I
resize the table to match all the rows (including header) it doesn't
help when I add a new row. I'm certain that, until a few days ago, I
could just start typing values in a new blank row and the table would
resize automatically.

Even when I resize the table LARGER than the existing data, when I
type in the first blank row the formulas still don't propagate
downward.

 

[Clif McIrvin]

It's 32-bit Excel 2010.

I have a table of date, deposit, withdrawal, balance, and some other
columns. Previously if I entered a date in the next row, the
formulas automatically propagated themselves to hat new row; now hey
don't. I don't know what I did to cause that change, but I'd be
grateful for guidance.

I tried resizing the able to contain all the existing rows, but that
didn't help: when I add a new row the formulas still don't copy from
the row above.

Sounds like you might have accidentally clicked on the "Stop
Automatically Extending Tables" option in the Auto-Correct context
menu -- or managed to turn off the 2010 Table somehow.

From the File tab: File | Options | Proofing | AutoCorrect Options...
select the AutoFormat As You Type tab --- on my system all three options
are checked.

I don't use "Tables" in 2010, but I do remember using "Lists" in 2003
sometimes. Two other things come to mind: did the workbook recently get
converted from 2003 Compatible mode into 2010 format? Or, did the
"Table" get turned off?

If your 2003 list is no longer a 2010 table, try making it a table
(Create tab | Table button).

HTH!

 

[Stan Brown]

Sounds like you might have accidentally clicked on the "Stop
Automatically Extending Tables" option in the Auto-Correct context
menu -- or managed to turn off the 2010 Table somehow.

From the File tab: File | Options | Proofing | AutoCorrect Options...
select the AutoFormat As You Type tab --- on my system all three options
are checked.

Thanks, Clif. All three are checked on my system also.

I don't use "Tables" in 2010, but I do remember using "Lists" in 2003
sometimes. Two other things come to mind: did the workbook recently get
converted from 2003 Compatible mode into 2010 format? Or, did the
"Table" get turned off?

The workbook is still an .xls. And the table is definitely a table:
when I click anywhere in it, the Table Tools Design tab appears.

If your 2003 list is no longer a 2010 table, try making it a table
(Create tab | Table button).

Insert tab, ITYM. But hat gives me an idea. I tried "Convert to
range" followed by "Insert Table", and that seems to have fixed the
problem. Thanks!

 

[Clif McIrvin]

Thanks, Clif. All three are checked on my system also.


The workbook is still an .xls. And the table is definitely a table:
when I click anywhere in it, the Table Tools Design tab appears.


Insert tab, ITYM. But hat gives me an idea. I tried "Convert to
range" followed by "Insert Table", and that seems to have fixed the
problem. Thanks!

Great! and yes, I meant the Insert tab.

IDK what setting actually gets changed if one clicks on that "Stop Auto
Expanding Tables" option in the AutoCorrect context / pull-down menu.

 

[StacySCB]

CAUTION!!! If you convert from a table, and you have conditional formatting and pivot tables set up you will loose all of them. Still looking for a fix that does not require me to loose the link to my 13 pivot table and my cond. formatting rules.