Auto-copy-down not working in Excel table

[robw]

Hi,
Has anyone any advice on this please....

As I understand it, when using tables (in Excel 2007 and beyond) hitting TAB when positioned on the bottom right cell of the table appends a new row to the table and automatically ""copies-down"" any formulae present in the columns of the table. This appears to work except for a column in which I have the following formula (taken from the bottom cell of the column) ; it does not auto-copy-down as expected , I merely get a blank cell in that column of the new row...and I end up having to copy down manually for that column.

=IF(COUNTA($B$3:B371)<
smooperiod/2,NA(),IF(B371=MAX(OFFSET(B371,-smooperiod/2,0,smooperiod,1)),B371,NA()))

As far as I can see it the column in question is in no way unusual wrt any other calculated column in my table for which auto-copy-down does work as expected.
Is it something to do with the formula itself ?(which BTW works as expected - it identifies local maximum points in a series)... Any thoughts guys?

Many thanks
Rob

 

[sergioMabres]

Hi Rob,
I checked your formula and the auto-fill worked just fine, so I think that you could re create the table just to see if the table is broken, I also when testing your formula I broke a table and instead of =IF(COUNTA($B$3:B371)... a previous version of the formula filled in, so I guess tables can be broken regarding auto-fill formulas.
And when things are broken, delete and recreate could solve the problem.
So you could try to convert the table to a range and then create the table again.
Cheers
Sergio

 

[robw]

Thanks Sergio,
I did just as you suggested .... and the problem simply went away (!?) .... some magic dust from the Excel fairy I suspect .
Anyway, many thanks for your interest and input....much appreciated.
Cheers
Rob