Dirty cells even though no changes???

[haw]

I have an Excel 2010 workbook (.xlsx) with a dozen or so worksheets in it. If I open and then immediately close the workbook, Excel asks me if I want to save my changes -- even though I haven't changed anything. I think I must have a formula in there somewhere that is causing this behavior, but I cannot find it. I know that I don't have any NOW() functions, but I'm not sure what else might cause this. Is there an easy way for me to find / pinpoint which cells are being marked dirty when the workbook is opened?

Thanks,
Adam

 

[Andrew Poulsom]

Are you using any other volatile functions?

http://www.decisionmodels.com/calcsecretsi.htm

 

[haw]

Thanks for the reply. I do use some of those functions occasionally, so one of them is probably the issue. I will look through my workbook for those and see if I can find the culprit. Thanks.

 

[haw]

OK, so I searched the workbook for all of those volatile functions listed in the link you gave me. And I did find that I was using INDIRECT, MATCH, INDEX, and SUMIF functions in the workbook. So I deleted all of these formulas. The workbook must still contain some volatile functions though, because it still asks me if I want to save the changes when I simply open and then close the workbook. I have now spent a few hours copying the workbook and deleting sheets and/or ranges of cells within sheets, trying to isolate the cells causing this ... but I haven't found them all yet and it is driving me crazy!

Do you have a VBA code snippet that will check whether a specific cell contains a volatile function or has been marked dirty -- so that I could loop through each cell in the workbook to identify which specific cells are causing this? This would expedite my troubleshooting...

 

[Andrew Poulsom]

Sorry no.