Why does formula not auto fill to next row?

[Steve Swift]

I have an xls with over 500 rows of data, every day I have to update the contents of some of the cells, Cell A contains the date and is auto filled already to the end of 2009, Cell B shows me the number of days since I began the sheet and is also auto filled already to the end of 2009, Cell C & Cell D I have to manually enter data

Cell E contains this formula =D527-D526

Cell F =C527/B526

Cell G = =IF(C527=0,0,C527-C526)

Cell H resorts to manual entry.

My question is "why do these columns with formulas, (E,F & G) not automatically carry the formula to the next row?" I'm sure that they once did. Is it a setting that I can't find?

This is excel 2007

Thanks in advance

Steve

 

[snowblizz]

There's nothing that will automatically copy the formulas down. I.e. Excel won't do it unless you somehow tell it to, drag/copy or a macro.
You might have had a macro that did it automatically? If it is an old Excel file then 2007 might have decided to close of macros in the file. Have you checked your security settings?

 

[JimM]

For the 2003 version you switch the function on in


Tools > Options > Edit Tab > Extend data range formats and formulas

Not sure if this is the same in 2007 though

HTH

Jim

 

[Steve Swift]

Hi Again,

I new about that setting in 2003 but could not find it in 2007. After some further digging around I did locate the setting, its here

In Excel 2007 click the button go to "excel options" then go to the "Advanced tab" and check "Extend data range formats and formulas"

Problem is that I have that ticked and it still does not work.

Any other thoughts anyone?

 

[btbankston]

I'm having the same problem with 2007. I've read that this version is supposed to automatically fill down formulas in cells adjacent to data when you refresh the dataset. My Excel, however, has suddenly stopped doing so. Previously, you could set this option in the dataset properties. Excel 2007 doesn't have the option in dataset properties due to it becoming a default option. If anyone finds a solution, I need it as well.

 

[ricardomadaleno]

hey guys,

Try this:

Excel Options > Proofing > AutoCorrect Options > Autoformat As You Type > Fill formulas in tables to create calculated columns

 

[GaryOfah]

Something is wrong with this operation I suppose. No one know what triggers it.
It could be fun if you can choose what exactly you want for as trigger..
This way sometimes it works, sometimes it don't...

At least that is my experience.
Not so far from other people experiences, I see.

I was thinking about this long time forgotten veiled secret to be somehow useful in my code?
http://www.mrexcel.com/forum/excel-...p-simple-but-fast-code-excel.html#post3412421

 

[husoi]

Something is wrong with this operation I suppose. No one know what triggers it.
It could be fun if you can choose what exactly you want for as trigger..
This way sometimes it works, sometimes it don't...

At least that is my experience.
Not so far from other people experiences, I see.

I was thinking about this long time forgotten veiled secret to be somehow useful in my code?
http://www.mrexcel.com/forum/excel-...p-simple-but-fast-code-excel.html#post3412421

Hi guys,

I think I "tripped" on the solution.

I use Excel tables a lot and the auto fill feature is great for large tables. The same happened to me and even weirder is that only filled some of the formulas leaving some of the cells without them. I mean in a new row, only some of the formulas are copied down but not all.
The way to solve it is, copy the formula from the top all the way to the bottom of the table. without changing the selection, scroll all the way up and you will see a small icon, click on it and you will have the option to automatically copy the formulas on new rows (sorry, can't remember the exact words). Click on it and you will have the table working properly again.

I think that this "fault" will happen when you replace values with formulas (or vice-versa) and Excel gets confused not knowing what to do next, then gets grumpy and decides to stop cooperating

 

[alfranco17]

Thanks! That fixed it.

hey guys,

Try this:

Excel Options > Proofing > AutoCorrect Options > Autoformat As You Type > Fill formulas in tables to create calculated columns