Changing
 

Edit|links|change source might be quicker.

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

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

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

Trevor Williams wrote:

I have a number of cells referencing named ranges stored in external
workbooks e.g:

='C:\Clients\Client Name\Response Files\ClientFile.xls'!Groupconfidence

Currently I change each of these cells manually.

Is it possible to update this programmatically? Ideally I'd input the Route
Directory into a cell (A1), and the filenames into a separate cell (A2), and
then use something like the following to pull it all together.

=TEXT(CHAR(39)&A1&"\"&A2&CHAR(39) &"!Groupconfidence",)
(If I type this into a cell it doesn't recognise it as a linked file)

The linked cells will use this info to call the named ranges into the workbook
The named ranges always stay the same.

Hope that makes sense?!

Thanks

Trevor

--

Dave Peterson

Changing
 

Laurent's XLL addins are written in C++ and turned into worksheet
functions using Excel's C API.

This conversion can be automated with RapidXLL. RapidXLL_NET
automatically converts C/C++ headers into Excel Add-Ins and .NET
Libraries. This allows you to use the exact same code base within
workbook functions as you would use within any .NET GUI application (
be it written in C# or VBA.NET ).

Please visit http://www.RapidXLL.net for free code and samples.

Sincerely,
The RapidXLL Team



Dave Peterson wrote:
Edit|links|change source might be quicker.

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

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

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

Trevor Williams wrote:

I have a number of cells referencing named ranges stored in external
workbooks e.g:

='C:\Clients\Client Name\Response Files\ClientFile.xls'!Groupconfidence

Currently I change each of these cells manually.

Is it possible to update this programmatically? Ideally I'd input the Route
Directory into a cell (A1), and the filenames into a separate cell (A2), and
then use something like the following to pull it all together.

=TEXT(CHAR(39)&A1&"\"&A2&CHAR(39) &"!Groupconfidence",)
(If I type this into a cell it doesn't recognise it as a linked file)

The linked cells will use this info to call the named ranges into the workbook
The named ranges always stay the same.

Hope that makes sense?!

Thanks

Trevor

--

Dave Peterson