Pivot Table Summaries Getting #REF Error?

sarahsfocused

I have a few pivot tables set to update weekly, and seem to be refreshing the actual numbers just fine. However, I have some summary functions set up that have totally stopped working, instead giving a #REF Error:

I'm a little surprised, as it worked perfectly (with real cell references, not #REFs) when I originally set it up, and the pivot itself has no trouble populating updated data. It seems that when the pivot refreshed, though, my summary functions lost all valid references. Is there a way to keep this from happening? I would like to keep the pivots current, and the calculations based on it also current. Thanks for any advice!

sarahsfocused

Thanks, Georgie! Smartsheet Support was able to help. Being new to pivot sheets, I did not realize that updating them wipes out all rows and re-generates them. I needed to use an index/match formula to get the cell reference stable. Which does seem to have worked!

Georgie

Hi @sarahsfocused,

I noticed that you have a Support case open about this issue - please continue to work with the Support team as this sounds like unexpected behaviour that needs further investigation. If you can attach to the case a screen recording or additional screenshots (with any sensitive date hidden/removed) that show which cells should be referenced in the sheet summary formulas, that will help them to investigate.

Thanks,

Georgie

sarahsfocused

Thanks, Georgie! Smartsheet Support was able to help. Being new to pivot sheets, I did not realize that updating them wipes out all rows and re-generates them. I needed to use an index/match formula to get the cell reference stable. Which does seem to have worked!