• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell references not updating

A

aspiring_to_be_awesome

New Member
Hello Chandoo community,
I am stumped by an Excel file that is not updating cell references. Calculation has been set to automatic, and I have checked for circular references and there are none. The file is large (3.5MB) but not massive with some involved formulas. It's curious that the cell reference to another tab in the same worksheet is not updating in that in does not involve a calculation.

Has anyone ever experienced a problem like this?
 
Hi Like M. Thank you very much for this information. Having read the linked document you referred me to, I don't believe User Defined Functions are the culprit in that the spreadsheet creator did not use Visual Basic. It may be the number of volatile functions, for which the cell references are mapped into. Short of getting rid of the volatile functions (which would gut the file and make it worthless), should using Ctrl/Alt/F9 force the cell re-referencing of a recalculation on a separate worksheet tab?

What is the best way to determine if there are uncalculated cells in a file (or on a worksheet tab) and then how best to force the uncalculated cells to calculate?

Best regards,
Mark
 
Hi Mark ,

I have not come across any formula which Excel could not recalculate because of too many dependencies ( without giving an error message ) , but my knowledge is limited.

Can you post any sample formula which according to you is not recalculating ?

Narayan
 
if the workbook is not up to date, you should see a "Calculate" message in Status bar, at bottom of XL.

Side thought, you specifically said "not updating cell references."
Does this just mean that formulas are not reclaculating, like I thought, or does this mean that when you move the cell, the formula does not follow it?
 
Hello Luke M and Narayan,
I thank you both for your continuing feedback.

Based on Narayan's suggestion of posting a sample formula, I took a closer look at the problematic file in search of the culprit formula. Narayan raises a good question, why would a simple cell reference between worksheet tabs not update?

Luke M in answer to your question, the cell with the reference is not being moved.

Curiously, the user was getting the following error message... "that in the bottom left corner of the excel model there is a button “Calculate.” When you move your mouse over that button a description appears that reads, “Results of formulas may be out of date because Excel is not set to update automatically. Click here or press F9 to recalculate manually.” This is odd because if you go to the options menu, the automatic calculation option is selected."

I closed the file and then opened it back up. A circular reference error message appeared as the file was opening. I searched each tab (there literally 100 - it is a 5MB legacy file) to find which one had the circular reference. Then, based on the link that Luke M had sent me yesterday, I looked for volatile functions and found the following:

=MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),(LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))+1))

That is a mouthful for sure. In talking with the user, the sole purpose of the formula is to post the tab label as the header for the exhibit in the body of the worksheet tab.

Based on these findings and both of your input, my recommendation was first to range value the exhibit headers and to delete those volatile formulas. Then to delete the significant number of worksheet tabs that are simply prior versions of the worksheets. By eliminating the unnecessary volatile formulas and reducing the file size, I'm thinking that the dependency tree calculation sequence will be reduced to the point where the Excel file can complete the formula updates. Does that sound reasonable?

Thank you both very much for your help. Whether it works or not, I have gained knowledge of Excel's calculation process and to be wary of volatile functions. Therefore, thanks to you both, I am one step closer to becoming awesome at Excel!

Mark
 
Sounds like a good plan. I can't imagine having a workbook with that many sheets...seems like it would be a pain just to navigate. Let us know if you find anything more. :)
 
You must log in or register to reply here.
Back
Top