=indirect() only works when the sending workbook is open.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Todd Lietha wrote:

Thank you. One thing I don't understand. If I have the external workbook
referenced by [ProgressUpdate_"&$D$3&".xls] open, the reference resolves
fine. But if I don't have it open, I get a #REF error.

Any thoughts?

Thank you!

"Farhad" wrote:

Hi,

Here is another way:

=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D"&ROW()+5)

assumed you are entering this formula in the first row of your worksheet so
if it is not you have to make sure that you are making the number 6 with
ROW()+5 in your formula at the first row, for example if you are entering
this formula in the row 10 you sould put ROW-4 instead Row()+5 in the formula
above.

Thanks,
--
Farhad Hodjat


"Todd Lietha" wrote:

I need to use a value from a cell to buil a refernce to a cell in another
workbook. I've mostly figured out how to do it using INDIRECT but have hit
one snag.

Current:
=INDIRECT("[ProgressUpdate_"&$D$3&".xls]"&$D$3&"!D6")

where $D$3 contains the value to use in building the reference and D6 is the
cell in the other workbook that I need returned.

But, as I copy this formula down subsequent rows, I need the returned cell
to move down relatively. i.e If the above formula is in row 1 in the local
workbook and returns cell D6, when copy the formula to row 2 I need it to
return cell D7.

How can I do this?

Thanks in advance.

--

Dave Peterson