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?
Scott C Lyerly 
I’ve seen two causes to this (that are actually the same cause). Here’s my theory: If you have exactly the same formula in every cell in that ListColumn, the formula replicates. If you have even one cell that has something different, the formula doesn’t replicate. The two cases I saw were 1)someone (probably me) typed a value in one cell, replacing the formula and 2)someone changed the formula in one cell to be a different formula from all the others. I suggest pressing Ctrl+~ (tilde) to show formulas and scan down that column to see if anything is different.
I’ve played around with that and can’t seem to get it to work. Now, what’s really interesting is that, when I go to edit the first cell (or any cell, really, but I tend to start at the top), after making changes and hitting enter, I do NOT get the tiny little cell menu that gives you the option to Undo the calculated column. I can’t remember the proper name for that menu, but it’s not there at all. Which suggested to me that Excel has “forgotten” that the column is/was calculated. Which in turn suggests that there should be some way to turn the cell back on.
Of course, I could be off my rocker with that theory, but there it is.
It may not be a true calculated column. Best way to ensure it is would be to select the entire column and clear it (copy your formula somewhere safe first). Then, in a single cell in the problem child column, enter your formula. So long as you have the AutoCorrect setting at default to fill in formulas, the column should populate.
I’ve seen this as well, as Dick describes, where data is in a column, even formulas, but it’s not a true calculated column. The only way to really check if it’s a true calculated column or not is to inspect the contents of the file. I wrote a quick blog post about checking for that: http://exceltables.com/remembering-table-formulas/.
If that doesn’t fix it I’d love to look at it, if possible.