Results 1 to 10 of 10

Thread: Changing formula reference

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Changing formula reference

    Hello !
    I have a question that looks simple in my mind, and I hope Excel has the ability to perform this, as it would save me significant time.
    I will give a simple example:

    Let's say I have a formula =C1-B1
    For example C represents march and B represents february.
    Next month, my monthly variance will become D1-C1 (april vs march).

    I want to be able to have the reference in 2 cells and by changing the value in these 2 cells the formula would automatically.

    I would have in one cell : C
    and in another cell : B

    I would want to change the first cell to D and the 2nd to C, so that my formula updates.
    I know I could use the find & replace function but I am looking for an alternative solution to that which will save me a lot of time.

    Thank you very much !!

  2. #2
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    99
    Articles
    0
    Excel Version
    Excel 2010
    Hi,

    a first example could be:

    D2 houses C, D3 houses B

    Code:
    =INDIRECT(D2&1)-INDIRECT(D3&1)
    Hope it helps

  3. #3
    Thank you, this is working properly.

    I would like to apply this to another sheet (sheet2). For example if I want to determine the difference of C1 and B1, like in the example above, but for sheet2 , but still put C in D2 (sheet1) and B in D3 (sheet 1).
    What would the formula look like ?

    Thank you very much !!

  4. #4
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    99
    Articles
    0
    Excel Version
    Excel 2010
    Hi,
    Code:
    =INDIRECT("Sheet2!"&D2&1)-INDIRECT("Sheet2!"&D3&1)
    Hope it helps

  5. #5
    Thank you very much Canapone !!
    If anybody is aware of an alternate solution, please let me know !
    The function works fine, but as the file will be used by serveral users, I am worried that the formula is not very intuitive (when you read it) for people that don't know that function.
    Thank you

  6. #6
    I just tried to do the same formula but linking it to a different file:
    =INDIRECT("'[test.xlsx]Sheet1'!"&$D$2&1)-INDIRECT("'[test.xlsx]Sheet1'!"&$D$2&1)
    It works when the other file is opened. However, there is a formula error when I close the other file.
    Would somebody know how to solve it ?

    Thank you !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •