Excel table column default formula

[piethein123]

Hi, can someone help me with the following ;
Have a worksheet with data that is in a table. Untill recently one of the colums always needed to have the same formula in order to calculate a certain time period. Since last month this formula had to be adjusted a little bit as the timing baseline changed. I changed the formula , when i do everything manually it works perfect , but when i add a new line , the table keeps using the old formula (from before last month). i tried to copy paste the old line as values so that the formula was gone. but it keeps coming back when adding a new line.

Can anyone please help - it contains data of 3 years and around 5000+ lines.

 

[Joe4]

Welcome to the Board!

I believe with tables, any column that uses a formula, it expects the formula to be the same for every row in that table. So it is probably going to continue to use the original formula whenever you add a new line.

My thoughts that either you would need to:
- start over and create a new table that only uses your new formula
- remove the table feature and just make it a list, so you can have different formulas on different rows

If the original formula in all existing rows can be replaced with the new formula, perhaps you could just replace ALL the formulas.
Or, maybe take it out of table mode, adjust all your formulas, then make it a table again. Maybe that will reset your formula.

 

[piethein123]

Hi. Thought of those 2, but unfortunately not an option. When starting over with new formula it means that all the old values are incorrect for that time. The removing of the table is also not really an option as the whole table contains many complicated formulas from column K to AB.
I was hoping that it would be something ‘simple’ that I didn’t figure out yet perhaps in the settings of the file or something.

Also it is strange that when I remove all the old formulas and only use the pasted values, that still this formula comes back hunting even though it’s not in the column or anywhere in the file anymore.

Hope someone can help.

 

[Joe4]

I believe tables might remember the formula from when the table was first set-up.
Bottom line: if you want two different formulas in the same column, then I don't think you can use tables. You would probably need to take it out of tables and just make it a list.
If you needed/wanted to copy certain things down to new rows, you could use VBA to do that.

Note that you could possibly combine both your old and new formulas into one large formula.
Let's say that at row 100, you want to start using the new formula. Then you could structure your formula like this:

=IF(ROW()>=100, new formula here, old formula here)

 

[piethein123]

Hmm the combining at a specific row I didn’t think of yet. I’m gonna try that one and will let you know. Thanks

 

[Alex Blakenburg]

A table need to have the same formula applied to all rows for the autofill of the formula to work.
If you need different formulas for different periods then @Joe4 suggestion of having some criteria determine which formula to apply on which row would be the way to go.
You just need to find some criteria eg date or row no to use as a condition.

To apply the new formula and have it autofill you need to
• copy the cell that has the formula you want.
• hit Ctrl+<spacebar> - this will select the entire column of the tables databody
• Ctrl+V to paste it.
The formula should now autofill when you add a new row

 

[piethein123]

It worked. Thanks guys.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.

 

[Joe4]

Excellent!
Glad you got it working the way you need/want.