Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cell References across worksheets (2000 or XP)

    I have a workbook with a sheet labeled 'Report' that has financial info populated by another program. The program populates this worksheet with balances for all of my GL accounts. I have inserted a worksheet that uses cell references to point back to particular account balances on the 'Report' worksheet. So if Cell E17 on the 'Report' worksheet has a value of $100.00 then a cell on my new sheet that contains the reference =Report!E17 also contains the value $100.00 . The problem is that when the third party program updates the figures on the 'Report' worksheet it first deletes the sheet and then re-creates it. This causes my =Report!17 to become =#REF!E17.
    Unfortunately there is nothing I can do about how the program handles the updating of the 'Report' sheet so I have been forced to find a work around. To do this I have changed the cell reference in my new sheet to =INDIRECT(CELL("contents",E17)) and the cell E17 on my new sheet contains the text Report!E17 .
    This work around has resolved my problem however I'd like to change the value on my sheet from =INDIRECT(CELL("contents",E17)) so that it does not require the use of the CELL function and another cell to contain the value Report!E17. The problem is that the use of =INDIRECT(Report!E17) results in #REF.
    Does anyone know why this would happen?

    Thanks
    Ed Carden

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell References across worksheets (2000 or XP)

    indirect means what it says - it is an indirect reference to cell C via cell B. Cell B must contain the address of cell C - thats just how the function works.
    In your example INDIRECT(report!e17) , the function is looking for an address in E17 and is getting $100 which it can't resolve to an address.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell References across worksheets (2000 or XP)

    Ahhhh. That makes sense. DO you have any suggestions as to an alternative to my sutuation?

    Thanks!

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell References across worksheets (2000 or XP)

    You just need to put your reference in quotes, like this:

    <pre>=INDIRECT("Report!E17")
    </pre>

    Legare Coleman

Posting Permissions

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