Results 1 to 10 of 10

Thread: Changing formula reference

  1. #1

    Changing formula reference



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    97
    Articles
    0
    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
    97
    Articles
    0
    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 !

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    INDIRECT() doesn't work with closed files.

    Try instead this alternative:

    =INDEX([test.xlsx]Sheet1!$1:$1048576,2,CODE($D$2)-64)-INDEX([test.xlsx]Sheet1!$1:$1048576,2,CODE($D$3)-64)


    where D2 and D3 contain the column letters to reference... the 2's in the above code reference the row number in the other workbook that the values to subtract from each other are located in.


  8. #8
    Thank you NBVC.

    Since there will be multiple users of the file, this formula is to complicated for a simple substraction.. If that is the only option, I would have to keep the option of the Find-Replace function.

    Anyone has a simpler formula ?

    Thank you very much,

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Guil, I think that you're going to find that trying to create a formula to create a dynamic reference to cells in another workbook is very difficult. Particularly if that workbook is closed. I kind of doubt that anyone is going to come back with a formula that is much simpler than what you've been given.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    The other alternative is to perform the

    =INDIRECT("Sheet2!"&D2&1)-INDIRECT("Sheet2!"&D3&1) formula that Canapone gave you in the test.xlsx workbox and then use a simple formula in the active workbook =[test.xlsx]Sheet1!$X$1 where X1 is cell containing the INDIRECT subtration formula in the Test.xlsx workbook..


Posting Permissions

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