Data table results being converted to values by Excel

[robkenny]

My problem is this:

I have several data tables set up in my spreadsheet. For reasons I can not detect (it seems to be happening at random), individual rows within these tables are converting to fixed values. Obviously thereafter the tables do not work as intended.

The 'surviving' cells in the tables still look normal (eg "=TABLE(,H247)").

If I recreate the tables in the same location(s), they then work fine again.

The only thing I can think of that might cause this is if you accidentally copy a table onto itself. But that's not what's happening here, I'm not going anywhere near these tables and they're still getting broken.

The spreadsheet is not large - two tabs of roughly 1000 rows (not wide) and a couple of more minor tabs. Limited use of conditional formatting, and a few charts. I'm using Excel 2016.

Has anyone else had this problem? Am I doing something daft?

Thanks very much for your help,

Rob

 

[gallen]

Will be difficult if you cant pinpoint the exact moment the cells change. It may appear random but it won't be. Do you have any Macro code in your workbook?

 

[robkenny]

Gallen -

Thanks for coming back to me. I will experiment with causes.

I do have some code - but it seems unlikely to be the problem. It's just there to update hyperlinks in a table of contents. See below


PC = Range("Page_Count").Value
Range("ToC_Start").Select
For x = 1 To PC
Row_Val = ActiveCell.Offset(0, 1).Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Model!A" & Row_Val
ActiveCell.Offset(1, 0).Select
Next x

 

[robkenny]

Gallen, not sure if you're still paying attention to this - I've been away from the spreadsheet in question for a while, so apologies for the silence

However, I do now have one known trigger for this problem. When I delete rows or columns, this triggers the corruption of the data tables. It doesn't seem to matter if the deletion is above or below the tables in question (or to the L or R for column deletion).

Also, while undo reverts the deletion, it does not 'uncorrupt' the table.

Thanks for any thoughts you may have

Rob

 

[robkenny]

Oops, further to the above, it doesn't seem to be that the row/col deletions are triggering the corruption. Rather they are triggering a recalc of the detector formulas I have to figure out when corruption has happened.

By this I mean I have set up formulas to monitor the cells of the data tables (using Isformula to see if a table formula has become a value). For whatever reason, these formulas are not properly recalcing. Even when the cell they're watching has become a value, they're still showing 'OK'. The effect of the row/col deletion is to trigger a recalc of these (in a way that autorecalc and F9 doesn't for some reason), and this reveals the error that already existed in the data table