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?