Structured Reference Formulas Converted Back to Cell references

[paula_michele]

Hello,

I have a workbook that contains many sumifs formulas which use structured references. The data in in named table. I noticed however that the formulas are now no longer structured but are absolute cell references. Not only do they not read as cleanly but they are no longer dynamic so if I add data to the bottom of the table, it is not included in the calculations.

I am using Excel 2010 although the original file (before I added the sumifs may have been in an earlier version although it was working fine). Also, I did make a copy of the file and renamed it prior to noticing the changed formulas.

Can I easily convert back? How do I avoid this from happening again?

Thank you for any insight you can offer,
Paula

 

[Smitty]

Welcome to the Board!

Sounds like you accidentally converted the table back to a range (it's an option on the Table menu). It's easy enough to reformat as a Table, but you'll likely need to redo your Structured References.

HTH,

 

[paula_michele]

Welcome to the Board!

Sounds like you accidentally converted the table back to a range (it's an option on the Table menu). It's easy enough to reformat as a Table, but you'll likely need to redo your Structured References.

HTH,

Thank you Smitty, I was afraid that I would need to recreate formulas The table does not appear to have been converted and is definitely still intact. I just don't want this to happen again since this is a daily report so I will continue to add data daily.

 

[Smitty]

The table does not appear to have been converted and is definitely still intact.

If your structured references have been converted and the table doesn't auto-expand then it's not a table anymore, but it's probably a bit deceptive because the table formatting stays in place. To get the table functionality back you'll need to go format it as a table again.

As for how it happened, who knows? Probably some inadvertent keystrokes.

 

[paula_michele]

Thank you Smitty, I figured out that even though I was working in Excel 2010, the original report was created in an earlier version that did not support sumifs. So when I saved the report it automatically saved as type Excel 97-2003 which didn't recognize sumifs and so converted them to cell references. What I needed to do was save it as type Excel Workbook. I updated all my formulas back to structured, saved it correctly and all is working well now.

 

[Smitty]

Glad you got it sorted out!