how to debug formulas

[bigdan]

I'm at a new job, have learned how to work with some excel files. With the files I'm working on there's 2 main tabs, Input (where I do my work) and Entry. In the Entry tab I just see an output which comes from the Input tab.

So far simple enough. The problem I have is at some points some data in the Entry tab doesnt appear. I get that #REF! error. I want to figure out how to decode this. I dont know if this is even something that CAN be explained on a forum, but if it can I'd be grateful. I'm not looking for you to tell me what this means in this case, I'm trying to figure out when there's sooooo many variables / dependencies in a formula how do you even start going about trying to figure it out. If its a few words I can do so but if you look at this really nasty piece of text you kinda get an idea.

The formula which sometimes gives me problems is:

=IF(U18="","",IF(AND(INPUT!$C$9="",INPUT!$C$9="Store to Store Tfr"),INPUT!N2&" "&INPUT!$C$9,IF(INPUT!$C$9="",INPUT!M2&" #"&INPUT!N2,INPUT!$C$9)))

Remember Input is the Input tab.

 

[ChrisOswald]

In excel 2007 (I think this is true for 2010 also), on the Formulas tab there is a section labeled "Formula Auditing." I find the Evaluate Formula option to be the most useful, personally.


Some more comments.
Because the formula you posted doesn't contain anything saying #REF!, one of the precedents is (most likely) using as an input a cell that was deleted. Use trace precedent to find it, or use evaluate formula to step through the formula to find the cell.


I do see a problem with the formula posted. In your second nested if your logical condition is

AND(INPUT!$C$9="",INPUT!$C$9="Store to Store Tfr")

which will always return false -- how can 1 cell both be empty and contain a value of "Store to Store Tfr" at the same time? I suspect that the person who created the spreadsheet meant to use OR(), but that's something you'll need to look into.