INDIRECT doesn't retain data from external worksheets
 

I'm using the INDIRECT function to bring in data from other spreadsheets.
Using this allows me to just populate a cell and automatically point to a new
spreadsheet (I have dozens of spreadsheets that hold similar data, and dozens
of formulas that pull that data over, so I don't want to manually edit all
the formulas).

However, it appears that when I use this, the file that it points to needs
to be open. I have a fully qualified path ("C:\Documents and
Settings\......"), so I don't understand why the document needs to be open.
If I don't use INDIRECT, and manually edit the text, it does not require the
spreadsheet to be open.

Is this correct behavior? If so, is there a workaround?

Thanks,

Pete

INDIRECT doesn't retain data from external worksheets
 

Unfortunately, this is correct behavior. INDIRECT does not function on
unopened external workbooks.

Possible workaround:
If you did create linking formulas that references the external workbook
directly, you can quickly change the link source from the Edit - Links
dialogue. No need to "manually edit" all the formulas.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PeteJ" wrote:

I'm using the INDIRECT function to bring in data from other spreadsheets.
Using this allows me to just populate a cell and automatically point to a new
spreadsheet (I have dozens of spreadsheets that hold similar data, and dozens
of formulas that pull that data over, so I don't want to manually edit all
the formulas).

However, it appears that when I use this, the file that it points to needs
to be open. I have a fully qualified path ("C:\Documents and
Settings\......"), so I don't understand why the document needs to be open.
If I don't use INDIRECT, and manually edit the text, it does not require the
spreadsheet to be open.

Is this correct behavior? If so, is there a workaround?

Thanks,

Pete

INDIRECT doesn't retain data from external worksheets
 

See this:

http://groups.google.com/group/micro...443753560f0075

--
Biff
Microsoft Excel MVP


"PeteJ" wrote in message
...
I'm using the INDIRECT function to bring in data from other spreadsheets.
Using this allows me to just populate a cell and automatically point to a
new
spreadsheet (I have dozens of spreadsheets that hold similar data, and
dozens
of formulas that pull that data over, so I don't want to manually edit all
the formulas).

However, it appears that when I use this, the file that it points to needs
to be open. I have a fully qualified path ("C:\Documents and
Settings\......"), so I don't understand why the document needs to be
open.
If I don't use INDIRECT, and manually edit the text, it does not require
the
spreadsheet to be open.

Is this correct behavior? If so, is there a workaround?

Thanks,

Pete