Answered by:
excel 2007 is changing my formulas why
Question

when I open my excel file, I want it to recalculate my values.
However, when I open excel it changes my formulas...
why?
I am using an excel addin
and I can pull data and it works fine.
but when I close the file and up it up again
my formulas changes and I do not know why.
oh when the formulas changes it do not pull any data
Thursday, September 12, 2013 5:16 PM
Answers

Ah! :)
It is not changing the formula, it is simply referencing the AddIn. It is a default behaviour of excel. Here is an example to replicate it
Open two excel files (Say Book1.xlsx and Book2.xlsx) and then in A1 of Book2.xlsx, type "=" and then select A1 of Book1.xlsx. You will see a formula like `=[Book1]Sheet1!$A$1` Now close both file and just open Book2.xlsx. Don't open the other. You will notice that the formula is now shown as something like this `='C:\[Book1.xlsx]Sheet1'!$A$1` Which is simply normal. That's excel's way of understanding where to look for the values. If you now open `Book1`, you will see the formula changing back to `=[Book1]Sheet1!$A$1`
Similarly in your case, the formula is referring to the file in "c:\Program file (x86)" which has the function LIMSGETDATA.
If you double click the AddIn or install the AddIn, then you will not see the change in the formula :)
Hope this answers your query?
 Proposed as answer by Asadulla JavedEditor Friday, September 13, 2013 6:04 AM
 Marked as answer by Btb4198 Saturday, September 21, 2013 4:39 PM
Thursday, September 12, 2013 7:01 PM
All replies


I have two excel addin
One addin is for Honewell PHD and the other addin is for Honeywell LIMS
The PHD formula from the addin is working, when I closed the file and opened it again. The PHD formulas works. Excel does not changed the formula.. and it updates the value ...
However the LIMS addin when I close excel and open it again excel changes the formulas.
Why? And how do I stop it?
also Excel does changed the formulas.
I had two different formulas from the LIMS Addin and when I change one formulas excel changed the other one as well.
 Edited by Btb4198 Thursday, September 12, 2013 5:36 PM
Thursday, September 12, 2013 5:35 PM 
>Why? And how do I stop it?
Why? Well, I don't know :) Without seeing the AddIn code, I won't be able to comment. If the AddIn is changing the formulas then to stop it you need to amend the code but I am guessing you do not have access to the code. So in such a scenario, you will have to contact the AddIn creators.
Excel by default will not change your formulas until and unless you are referring to a range/sheet/workbook which is not there. In such a case you will see REF# errors in your formula
 Edited by Siddharth Rout Thursday, September 12, 2013 5:48 PM
Thursday, September 12, 2013 5:41 PM 
look how excel is changing the formulas after the excel file is reopened . Why is it adding "c:\Program file (x86) ..." to the formulas? again the addin works, I can pull data, but once I close the excel file and reopen it. excel changes the formulas to that. I do not understand.
any ideas ?
Thursday, September 12, 2013 6:37 PM 
Ah! :)
It is not changing the formula, it is simply referencing the AddIn. It is a default behaviour of excel. Here is an example to replicate it
Open two excel files (Say Book1.xlsx and Book2.xlsx) and then in A1 of Book2.xlsx, type "=" and then select A1 of Book1.xlsx. You will see a formula like `=[Book1]Sheet1!$A$1` Now close both file and just open Book2.xlsx. Don't open the other. You will notice that the formula is now shown as something like this `='C:\[Book1.xlsx]Sheet1'!$A$1` Which is simply normal. That's excel's way of understanding where to look for the values. If you now open `Book1`, you will see the formula changing back to `=[Book1]Sheet1!$A$1`
Similarly in your case, the formula is referring to the file in "c:\Program file (x86)" which has the function LIMSGETDATA.
If you double click the AddIn or install the AddIn, then you will not see the change in the formula :)
Hope this answers your query?
 Proposed as answer by Asadulla JavedEditor Friday, September 13, 2013 6:04 AM
 Marked as answer by Btb4198 Saturday, September 21, 2013 4:39 PM
Thursday, September 12, 2013 7:01 PM 
I did your example and it worked just like you said. So I get that.
in your example I enter the value 8 for A1 of book3 and A1 in book2 pointed / referred to A1 of book3. And A1 of book2 had the value of 8 as well.
now when I closed both of them book3 and book2. and openned only book2 is has the function "= 'C:\Users\E574129\Desktop\[Book3.xls]Sheet1'!$A$1"
but it still has the value 8 in A1 of book2.
now when I open book3 , book2 have the function " = [Book3.xls]Sheet1!$A$1 " but it still has the value 8 in A1.
so that is all good and it works and I get that..
but
why with the addin it do not still show the value ?
If I redo the function using the addin it will pull the data and work but if I save, close it, and re open it excel will not return any value .
also with the other excel addin , the one that works when i close excel and open excel again, that function never changes .. I have checked..
why does that one not work ?
and how can i fix it ?
Also, both add in are installed in excel .
how should I troubleshoot this ?
Thursday, September 12, 2013 9:21 PM 
what do you mean by install the Addin?
Friday, September 13, 2013 3:37 AM