| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel cells changing to "#VALUE!" whenever another file is opened
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is opened
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 On Mar 9, 9:34*pm, kk wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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 On Mar 10, 1:49*pm, kk wrote: 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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! .- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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" wrote: 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 On Mar 10, 1:49 pm, kk wrote: 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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! .- Hide quoted text - - Show quoted text - . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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 On Mar 10, 3:26*pm, kk wrote: 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" wrote: 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 On Mar 10, 1:49 pm, kk wrote: 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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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 "Pete_UK" wrote: 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 On Mar 10, 3:26 pm, kk wrote: 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" wrote: 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 On Mar 10, 1:49 pm, kk wrote: 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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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 "Pete_UK" wrote: 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 On Mar 10, 3:26 pm, kk wrote: 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" wrote: 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 On Mar 10, 1:49 pm, kk wrote: 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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cells changing to "#VALUE!" whenever another file is ope
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... "kk" wrote: 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 "Pete_UK" wrote: 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 On Mar 10, 3:26 pm, kk wrote: 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" wrote: 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 On Mar 10, 1:49 pm, kk wrote: 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" wrote: 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 On Mar 9, 9:34 pm, kk wrote: 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! .- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "File Error: Data May Be Lost" when file saved on Mac is opened inWindows | Excel Discussion (Misc queries) | |||
| Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
| Automatic "data refresh" when XLS file is opened? | Excel Discussion (Misc queries) | |||
| Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
| Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
Linear Mode
