How do you use the INDIRECT function successfully to access data on another sheet tab?

[Kim]

Hi,

I use the INDIRECT function to access data in the one sheet
successfully but when I try to access data in the same file but on
another sheet tab, I get the REF! error.

Can anyone assist with this?

Typically my command is of the nature:

=INDIRECT("+'[source data workbook.xls]Detail'!"&$A$1&$A$2)

but results in an error.

Thanks guys,

Kim

 

[JE McGimpsey]

If it's in the same file, don't put in the workbook name (and don't for
whatever reason, use "+" as a prefix):

=INDIRECT("Detail!" & A1 & A2)

 

[Earl Kiosterud]

Kim,

Remove the + from the formula. Make sure that A1 contains the column
address (e.g.: A) and A2 contains the row address (e.g.: 1).

If the sheet is in the same workbook, you can skip the workbook name:
=INDIRECT("Detail!" & A1 & A2)

 

[Dave Peterson]

And just to add...

If your worksheet needs apostrophes surrounding it (when it has spaces in the
name):

=INDIRECT("'Detail 999'!"&A1&A2)

And =indirect() won't work if the other sheet is in another workbook and that
other workbook is closed.

It'll work if that other workbook is open, though.

Hi,

I use the INDIRECT function to access data in the one sheet
successfully but when I try to access data in the same file but on
another sheet tab, I get the REF! error.

Can anyone assist with this?

Typically my command is of the nature:

=INDIRECT("+'[source data workbook.xls]Detail'!"&$A$1&$A$2)

but results in an error.

Thanks guys,

Kim