CODEWITHSUNDEEP
excel
jitendragarg
jitendragarg

Reputation: 957

Excel formula never evaluates

I have a bunch of spreadsheets in an excel. There is a sheet called "PreLim" that reads the value from other sheets, if they exist. 

=IF(IFERROR('Vessel Reading 2'!L3,"")<>"",IFERROR('Vessel Reading 2'!L3,""),"")

This is one of those formulas. Initially, there is only one spreadsheet named "Vessel Reading 1", so this formula returns nothing. User normally copies this same sheet to create multiple, in which case it creates a new sheet named "Vessel Reading 1 (2)". At which point user renames the sheet to "Vessel Reading 2". Now when user enters the information in L3 cell in "Vessel Reading 2", formula never evaluates.

Am I missing something silly here that is causing this re-evaluation to fail? I already checked and re-evaluation is set to automatic.

Upvotes: 0

Views: 119

Answers (1)

XOR LX
XOR LX

Reputation: 7762

Re-committing the formula be pressing ENTER will force Excel to evaluate the formula.

If this level of manual intervention is not satisfactory, you could replace all instances of:

'Vessel Reading 2'!L3

with:

INDIRECT("'Vessel Reading 2'!L3")

though you should be aware that INDIRECT is a volatile function.

A simpler, non-volatile solution would be to remove the equals sign from any formulas referencing the as-yet-created worksheet, thus rendering them non-formulas. Once that new worksheet comes into existence, simply re-insert the equals sign for each formula.

If you have a lot of such formulas, you can perform a bulk Find & Replace, replacing "=" with e.g. "#" (or any suitable string which will not occur naturally in any of the formulas). After creation of the new worksheet, simply perform the reverse Find and Replace.

Regards

Upvotes: 2

Related Questions