locked
excel 2007 is changing my formulas why RRS feed

  • 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 Add-In. 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 Add-In or install the Add-In, then you will not see the change in the formula :)

    Hope this answers your query?


    Sid

    • 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

  • Excel by default doesn't change your formulas. Maybe it is the Add-in which is changing the formulas? What kind of Add-In is it?

    Sid

    Thursday, September 12, 2013 5:24 PM
  • 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 Add-In code, I won't be able to comment. If the Add-In 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 Add-In 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


    Sid


    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 add-in 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 Add-In. 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 Add-In or install the Add-In, then you will not see the change in the formula :)

    Hope this answers your query?


    Sid

    • 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 Add-in?


    Friday, September 13, 2013 3:37 AM