Problem with INDIRECT formula

**DaveLearner** · 03-17-2013, 08:03 AM

Hi Guys,

I am trying to create a formula to copy data from D8 in another worksheet to the master copy. So far I have the following formula "=INDIRECT("'Invoice "&N506&".xlsm'!""D8")".
The idea is to reference the worksheet by using "Invoice "&N505&" to pick the worksheet referred to in column N, row 505 and the data in cell D8 on that worksheet. By using this method I will be able to copy by dragging the formula. The problem I am having is that the formula is coming back with REF! I have tried to adjust the formula in various ways but have not succeeded in solving this problem. Any help much appreciated.

**Spencer101** · 03-17-2013, 08:11 AM

Are you looking at a different workSHEET or a different workBOOK?

If a different sheet in the same book then the below should work.

=INDIRECT("'Invoice "&N505&"'!D8")

**Kevin UK** · 03-17-2013, 08:38 AM

Hi DaveLearner

Try
=INDIRECT("'[The Workbook]Invoice "&N506&"'!$D$8")

**DaveLearner** · 03-17-2013, 10:16 AM

Thanks Spencer 101 it works a treat. I was looking at different worksheet in same workbook. I now copy a copy of the front sheet from the relevant workbook into the Master sheet.
I had tried looking at the worksheet in the original closed workbook using "=INDIRECT.EXT("'I:\Invoices\["&N505&" Invoice.xlsm]Invoice'!D8")" but could not get it to run. Tell a lie it did run once but upon reopening values were shown as REF! and could not get them to update hence the reason for applying the formula you have helped me with. Thanks again.