I know the title of this post is too long. But it captures the essence of what I’m trying to figure out.

I’m sure this has happened to someone else, and maybe even someone else who has figured out how to correct it. If you have, let me know, leave a comment below, send a smoke signal, anything to help me close the loop on this thing.

Here’s the problem: in a list object, or table as they’re often called, on occasion a field that is normally calculated stops doing just that. More clinically, the formula replication functionality in Excel coughs twice, falls down in the dusty road of the information highway, and gives up the ghost. Not on all tables, mind you. Not even on all columns in the same table. In specific columns only.

I’m on the hunt to figure out why.

The primary problem in searching for an answer is that you can’t quite find what you’re looking for through Google. Or maybe its just me, and that I could very easily suck and searching the interwebs. When I try searching for words such as “list object calculated column not working”, or “replicate formula no longer working”, I always find myself awash in a sea of posts about how to change the setting in Options for replicating formulas. That’s not what I need. I also find a lot of posts on how to turn Calculation back on in a workbook. Not what I need. I know these two things already. I need something else.

I checked to make sure that the cell isn’t formatted for Text. If you’ve ever formatted a column as Text and tried to change the formatting back to Number or General, you know that Excel can occasionally develop amnesia about how formulas work in those cells. Once you turn a cell to Text, changing it back is a pain in the butt given that Excel likes to keep treating formulas as if they’re Text. No, that’s not the issue I have here.

I tried adjusting the Options settings. I turned the Replicate Formulas option off, then back on. I had a hope that this would essentially pop the clutch on these calculated columns. No dice. I tried doing it through the code. Also no dice.

I could have spent all day rotting this thing out, but guess is that is never actually find it. It was simpler to build a new table with the same columns and move my data into it. Which is what I did.

Still, I figure there has to be a means of resetting a calculated column. Anyone? Anyone? Bueller? Bueller?