Cell References across worksheets (2000 or XP)

[edcarden]

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

[Bryan]

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.

[edcarden]

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

Thanks!

[LegareColeman]

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

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