excel cells changing to "#VALUE!" whenever another file is opened

[kk]

I have an excel file and formulae in two of the columns is changing to
"#VALUE!", whenver a new file is opened and cells updated with data in the
new file. All other cells are not effected and the only difference is, these
two columns have Vookup function(to another sheet in the same file
only).Also, pressing F2 in each cell and then pressing enter would calculate
again. I have automatic calculation turned on.

I could not understand why any change in a separate new file would mess up
the formula in this file?Please help!

 

[Pete_UK]

I suspect that the VLOOKUP formula is returning a text value, on which
you are trying to carry out some arithmetic. Check out the data in
your new file, and if you are still stuck then post back with more
details.

Hope this helps.

Pete

 

[kk]

Thanks for your response. Actually, the Vlookup function is linked to the
data in another sheet in the same file.So why would it get effected whenever
any changes are made in a different file?

 

[Pete_UK]

I don't know until you post some details of what data you have, how it
is linked to the new file, formulas used etc.

Pete

 

[kk]

pete,
The file I am having the issue is not linked to the new file.So why
would a change in any NEW workbook file would mess up the Vlookup formulaes
in my file?

Also, reg details...the file I was working on has VB functions and the cells
calling for VB function(which use the Vlookup and is only linked to another
worksheet in the same workbook file ) are changing to "#value!" whenever
changes are done to any new file opened.I would think there would not be any
problem with the VB code and data in my file as the formulaes are updating
correctly when I press F2 and enter( in each of the cells that got messed up)

 

[Pete_UK]

Tell me what formula is in the cells that produce #VALUE. If they
refer to some other cells, tell me what is in those too. I'm not a
mind reader, and I cannot see your workbook(s) or data.

Pete

 

[kk]

I did not go to the details of the code and formula as I thought the problem
might be related to excel settings but we never know...well here is the
formula for the cells changing to #value
IF(AC7="1/6","NA",IF(AC7="1/3","NA",IF((AE7*100)<=40,(TEVSelect(M7,K7,P7,Q7,Z7,R7,S7,ValveCapLo(AC7,M7,S7),J7,$AG$4)),"NA")))

ValveCapLo function uses the lookup and code is
Function ValveCapLo(Valvecap As String, Refrg As String, Valvetype As
String) As String

Dim row As Integer

If (Valvetype = "sq" ) And (Refrg = "22" ) Then
row = Application.VLookup(Valvecap, Range("Data!A3:C9"), 3, False)
ValveCapLo = Application.VLookup(row - 1, Range("Data!C3:E9"), 2, False)
Else
ValveCapLo = 0
End If

End Function

 

[kk]

Here is the data in the data!sheet which the function is refering to
valve cap row
1/3 3
3/4 4
1 5
1-1/2 6
2-1/2 7
3-1/2 8
5 9

 

[kk]

pete,
just fyi..want to let you know...For some reason excel is getting
confused with two vlookups in the same function and after simplifying to use
one vlookup..everything works great...