Indirect concatenated link doesn't work with UNC

[Lynn]

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.

To make it even more frustrating, if I copy the cell that doesn't work, then
edit the copy changing x: to \\server\share, and hard code in the variable
instead of concatenating, it works again.

From reading other posts, this should work. What am I missing?

 

[Dave Peterson]

If workbook A (the sending workbook, right?) is open then you don't need the
path (UNC or mapped drive).

And if the sending workbook is closed, then the =indirect() won't work anyway.

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.

To make it even more frustrating, if I copy the cell that doesn't work, then
edit the copy changing x: to \\server\share, and hard code in the variable
instead of concatenating, it works again.

From reading other posts, this should work. What am I missing?

 

[Lynn]

Dave, That doesn't accomplish my goal. I can't enter a reference to an
external cell without either the path, or another workbook open (A, in this
case). But if I open workbook A first, then set up the formulae, even without
specifying the path it eventually hard codes to that particular A, no matter
what order I close or save things in. I need it to go to whatever version of
that workbook A is open.

Here's what I'm trying to do: A and B both summarize the same information,
just differently. A is generated by another program, B is our standard
in-house summary. Information in B is organized so as to match input required
by another department; A is not. Output of the other program is organized so
that every project has its own folder, and each folder has workbook A in it
(all named identically), but with data for that project. Path to every A is
identical except for the project number, including file name. I need to set
up B so that it will pull numbers from the A file in the path specified by
the user's input of project number.

Did I explain that well enough? And if so, how do I accomplish it, other
than use the one method I've found so far that works and make sure all users
have the same drive letter mapped to the source share?

Thanks again....

If workbook A (the sending workbook, right?) is open then you don't need the
path (UNC or mapped drive).

And if the sending workbook is closed, then the =indirect() won't work anyway.

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.

To make it even more frustrating, if I copy the cell that doesn't work, then
edit the copy changing x: to \\server\share, and hard code in the variable
instead of concatenating, it works again.

From reading other posts, this should work. What am I missing?

 

[Dave Peterson]

First, you can't refer to a cell in a different file if that sending file is
closed and use =indirect().

Second, the stuff ("'x:\folder\folder\") inside the =indirect() function is a
string. This would always point at the X:\folder\folder location.

I tried this in a test workbook (book1.xls).

I created a new workbook and put "Root" in A1 of sheet1.
Then I saved this workbook as C:\book2.xls

Then I changed A1 to Excel and saved a copy in C:\excel

Then in A1 of Book1.xls, I added this formula.
=INDIRECT("[book2.xls]Sheet1!$A$1")

It returned Excel (since that version of book2.xls was open).

I closed c:\excel\book2.xls and recalculated. I got a #REF! error
(recalculating is important).

Then I opened c:\book2.xls and saw that Root was returned.

So you could drop all the drive/path info from your formulas as long as you
explain that the user MUST open the correct file first.

If they open a file with the same name, but from a different folder, excel will
go happily along. Your users won't get the results they want, but excel won't
care.

==================
On the other hand, if you want to keep the sending workbook closed, you can't
use =indirect() in your function call.

That means you'll have to either tell them to create the formula themselves (or
provide a macro that does the work for them).

Or you could use a function called =indirect.ext() written by Laurent Longre.
(Everyone will have to have a copy of this function/addin, too.)

You can find it in the morefunc.xll addin:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

(I didn't test this, so you'll have to watch out for typos.)

=INDIRECT.ext("'" & $a$1 & $b$1 & $C$1 & "\[filename.xls]Sheet1'!A1")

Where
A1 holds the drive (either X: or the UNC share--\\something\otherthing)
B1 holds the folder (\folder\folder)
C1 holds the input (whatever that is)

Dave, That doesn't accomplish my goal. I can't enter a reference to an
external cell without either the path, or another workbook open (A, in this
case). But if I open workbook A first, then set up the formulae, even without
specifying the path it eventually hard codes to that particular A, no matter
what order I close or save things in. I need it to go to whatever version of
that workbook A is open.

Here's what I'm trying to do: A and B both summarize the same information,
just differently. A is generated by another program, B is our standard
in-house summary. Information in B is organized so as to match input required
by another department; A is not. Output of the other program is organized so
that every project has its own folder, and each folder has workbook A in it
(all named identically), but with data for that project. Path to every A is
identical except for the project number, including file name. I need to set
up B so that it will pull numbers from the A file in the path specified by
the user's input of project number.

Did I explain that well enough? And if so, how do I accomplish it, other
than use the one method I've found so far that works and make sure all users
have the same drive letter mapped to the source share?

Thanks again....

If workbook A (the sending workbook, right?) is open then you don't need the
path (UNC or mapped drive).

And if the sending workbook is closed, then the =indirect() won't work anyway.

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.

To make it even more frustrating, if I copy the cell that doesn't work, then
edit the copy changing x: to \\server\share, and hard code in the variable
instead of concatenating, it works again.

From reading other posts, this should work. What am I missing?

 

[Harlan Grove]

. . . I can't enter a reference to an
external cell without either the path, or another workbook open (A, in this
case). But if I open workbook A first, then set up the formulae, even without
specifying the path it eventually hard codes to that particular A, no matter
what order I close or save things in. I need it to go to whatever version of
that workbook A is open.

....

If you create STATIC external references into OPEN workbooks, they'll
omit the workbook's path. When you close that workbook, Excel will
include the path to the workbook in the external references. That
*ONLY* works for STATIC external references.

Dynamic external references using INDIRECT can *ONLY* refer to ranges
in OPEN workbooks.

This is a syntactic limitation of Excel. Excel resolved static and
dynamic external references into OPEN workbooks as range objects, but
it resolves static external references into CLOSED workbooks as single
values or arrays BUT NOT RANGE OBJECTS. Excel's INDIRECT functions can
ONLY return range objects, and that's the reason why it can't resolve
external references into closed workbooks.

You mention that you have a working solution that just requires that
all users employ the same drive mapping. I'm not sure I see how that
would work when users change product IDs. If the workbooks you need to
access are all named A.xls but in different directories, if they're
OPEN, then formulas like

=INDIRECT("'[A.xls]XYZ'!C5")

will refer to the XYZ!C5 cell in whichever A.xls is open, no matter
what that A.xls file's path may be. OTOH, if no A.xls file is open,
it'll return #REF!, and there's no way to have it return anything else.

 

[Dave Peterson]

ps.

Another way to do this is to provide a macro that would build the formula based
on values that the user supplied--maybe in a userform or maybe in cells.

Then plop those "prebuilt" formulas into the range. They would be no need for
the =indirect() or =indirect.ext() functions. The macro would build the correct
formula just like there was only once choice.

You could give them the cells (with instructions/data|Validation) and when
they've finished, they could click a button to populate the cells with the
formulas that retrieve the values from the specified location.

======
Another way if there are only a few workbooks and those workbooks don't change
name/location.

Populate another worksheet (hide it when you're done). Then the cells you
really want populated can retrieve the data from this hidden worksheet.

This may work if there isn't too much data to retrieve. But once you start
retrieving more than a little data across a network, you may find that it seems
to take forever.



Dave Peterson wrote:
<<snipped>>

 

[Lynn]

OK, so what I'm missing is apparently a few brain cells. It's been one of
those weeks. I'm still missing Monday, think yesterday was Monday and today
Tuesday. Guess that's better than thinking today's Thursday!

In your prior post, I missed the thought that I should still use Indirect
but drop out the path. My bad - big time. How complicated can I try to make
something this simple?

Some of your other ideas are no-go's. There are over 500 lines in this
summary sheet (B), many imports from the 'workbook A' all over the place, and
most of them sum numbers from various cells in A into one cell in B. So to do
it behind the scenes requires building a table listing, for each cell in B,
what cells in A to sum into it, then running however many iterations of the
value lookup are required, then summing those results - in addition to
getting project number and other information. And having users do their own
formulas? I'm just going to have them open the source by going through the
other program. Trying to explain how to navigate to it through Excel or
Explorer would be challenging.

An option I may explore down the road, once I get this working by the brute
force open-it-first method, is an 'on open' macro in B which asks for project
number then opens the correct version of A based on the input. Of course,
they'd need to know project number - and we know our projects by name, only
that other software package refers to it by the number. So they'd still have
to open the other software to get the project number!

I still don't know why the indirect function wouldn't work with UNC but did
work with mapped drive. That would make life easier. But I can live with this.

THANKS - both for the help and for beating me over the head until the
lightbulb went on.

First, you can't refer to a cell in a different file if that sending file is
closed and use =indirect().

Second, the stuff ("'x:\folder\folder\") inside the =indirect() function is a
string. This would always point at the X:\folder\folder location.

I tried this in a test workbook (book1.xls).

I created a new workbook and put "Root" in A1 of sheet1.
Then I saved this workbook as C:\book2.xls

Then I changed A1 to Excel and saved a copy in C:\excel

Then in A1 of Book1.xls, I added this formula.
=INDIRECT("[book2.xls]Sheet1!$A$1")

It returned Excel (since that version of book2.xls was open).

I closed c:\excel\book2.xls and recalculated. I got a #REF! error
(recalculating is important).

Then I opened c:\book2.xls and saw that Root was returned.

So you could drop all the drive/path info from your formulas as long as you
explain that the user MUST open the correct file first.

If they open a file with the same name, but from a different folder, excel will
go happily along. Your users won't get the results they want, but excel won't
care.

==================
On the other hand, if you want to keep the sending workbook closed, you can't
use =indirect() in your function call.

That means you'll have to either tell them to create the formula themselves (or
provide a macro that does the work for them).

Or you could use a function called =indirect.ext() written by Laurent Longre.
(Everyone will have to have a copy of this function/addin, too.)

You can find it in the morefunc.xll addin:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

(I didn't test this, so you'll have to watch out for typos.)

=INDIRECT.ext("'" & $a$1 & $b$1 & $C$1 & "\[filename.xls]Sheet1'!A1")

Where
A1 holds the drive (either X: or the UNC share--\\something\otherthing)
B1 holds the folder (\folder\folder)
C1 holds the input (whatever that is)

Dave, That doesn't accomplish my goal. I can't enter a reference to an
external cell without either the path, or another workbook open (A, in this
case). But if I open workbook A first, then set up the formulae, even without
specifying the path it eventually hard codes to that particular A, no matter
what order I close or save things in. I need it to go to whatever version of
that workbook A is open.

Here's what I'm trying to do: A and B both summarize the same information,
just differently. A is generated by another program, B is our standard
in-house summary. Information in B is organized so as to match input required
by another department; A is not. Output of the other program is organized so
that every project has its own folder, and each folder has workbook A in it
(all named identically), but with data for that project. Path to every A is
identical except for the project number, including file name. I need to set
up B so that it will pull numbers from the A file in the path specified by
the user's input of project number.

Did I explain that well enough? And if so, how do I accomplish it, other
than use the one method I've found so far that works and make sure all users
have the same drive letter mapped to the source share?

Thanks again....

If workbook A (the sending workbook, right?) is open then you don't need the
path (UNC or mapped drive).

And if the sending workbook is closed, then the =indirect() won't work anyway.



Lynn wrote:

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.

To make it even more frustrating, if I copy the cell that doesn't work, then
edit the copy changing x: to \\server\share, and hard code in the variable
instead of concatenating, it works again.

From reading other posts, this should work. What am I missing?

 

[Dave Peterson]

Glad things are sorted out.

You could have a macro that runs when the user opens the file and prompt the
user for the name/location of the "sending" file--or just sits there waiting for
them to click a button when they know what to use.

Good luck.

OK, so what I'm missing is apparently a few brain cells. It's been one of
those weeks. I'm still missing Monday, think yesterday was Monday and today
Tuesday. Guess that's better than thinking today's Thursday!

In your prior post, I missed the thought that I should still use Indirect
but drop out the path. My bad - big time. How complicated can I try to make
something this simple?

Some of your other ideas are no-go's. There are over 500 lines in this
summary sheet (B), many imports from the 'workbook A' all over the place, and
most of them sum numbers from various cells in A into one cell in B. So to do
it behind the scenes requires building a table listing, for each cell in B,
what cells in A to sum into it, then running however many iterations of the
value lookup are required, then summing those results - in addition to
getting project number and other information. And having users do their own
formulas? I'm just going to have them open the source by going through the
other program. Trying to explain how to navigate to it through Excel or
Explorer would be challenging.

An option I may explore down the road, once I get this working by the brute
force open-it-first method, is an 'on open' macro in B which asks for project
number then opens the correct version of A based on the input. Of course,
they'd need to know project number - and we know our projects by name, only
that other software package refers to it by the number. So they'd still have
to open the other software to get the project number!

I still don't know why the indirect function wouldn't work with UNC but did
work with mapped drive. That would make life easier. But I can live with this.

THANKS - both for the help and for beating me over the head until the
lightbulb went on.

First, you can't refer to a cell in a different file if that sending file is
closed and use =indirect().

Second, the stuff ("'x:\folder\folder\") inside the =indirect() function is a
string. This would always point at the X:\folder\folder location.

I tried this in a test workbook (book1.xls).

I created a new workbook and put "Root" in A1 of sheet1.
Then I saved this workbook as C:\book2.xls

Then I changed A1 to Excel and saved a copy in C:\excel

Then in A1 of Book1.xls, I added this formula.
=INDIRECT("[book2.xls]Sheet1!$A$1")

It returned Excel (since that version of book2.xls was open).

I closed c:\excel\book2.xls and recalculated. I got a #REF! error
(recalculating is important).

Then I opened c:\book2.xls and saw that Root was returned.

So you could drop all the drive/path info from your formulas as long as you
explain that the user MUST open the correct file first.

If they open a file with the same name, but from a different folder, excel will
go happily along. Your users won't get the results they want, but excel won't
care.

==================
On the other hand, if you want to keep the sending workbook closed, you can't
use =indirect() in your function call.

That means you'll have to either tell them to create the formula themselves (or
provide a macro that does the work for them).

Or you could use a function called =indirect.ext() written by Laurent Longre.
(Everyone will have to have a copy of this function/addin, too.)

You can find it in the morefunc.xll addin:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

(I didn't test this, so you'll have to watch out for typos.)

=INDIRECT.ext("'" & $a$1 & $b$1 & $C$1 & "\[filename.xls]Sheet1'!A1")

Where
A1 holds the drive (either X: or the UNC share--\\something\otherthing)
B1 holds the folder (\folder\folder)
C1 holds the input (whatever that is)

Dave, That doesn't accomplish my goal. I can't enter a reference to an
external cell without either the path, or another workbook open (A, in this
case). But if I open workbook A first, then set up the formulae, even without
specifying the path it eventually hard codes to that particular A, no matter
what order I close or save things in. I need it to go to whatever version of
that workbook A is open.

Here's what I'm trying to do: A and B both summarize the same information,
just differently. A is generated by another program, B is our standard
in-house summary. Information in B is organized so as to match input required
by another department; A is not. Output of the other program is organized so
that every project has its own folder, and each folder has workbook A in it
(all named identically), but with data for that project. Path to every A is
identical except for the project number, including file name. I need to set
up B so that it will pull numbers from the A file in the path specified by
the user's input of project number.

Did I explain that well enough? And if so, how do I accomplish it, other
than use the one method I've found so far that works and make sure all users
have the same drive letter mapped to the source share?

Thanks again....

:

If workbook A (the sending workbook, right?) is open then you don't need the
path (UNC or mapped drive).

And if the sending workbook is closed, then the =indirect() won't work anyway.



Lynn wrote:

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
one cell in workbook B, where part of path to workbook A is a variable input
by the user. If I use
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
thing, different cell), all is well. If I change x: to the UNC path
(\\server\sharename, and I know I have the right server and share names) I
get #REF! error. Workbook A is already open in the same instance of Excel.

To make it even more frustrating, if I copy the cell that doesn't work, then
edit the copy changing x: to \\server\share, and hard code in the variable
instead of concatenating, it works again.

From reading other posts, this should work. What am I missing?

 

[Lynn]

I take it back, it doesn't entirely solve the problem. Saving B doesn't save
the values that were calculated, just the formula/function. And if the source
workbook A goes away (for example, is archived off active server), we won't
be able to retrieve the summary for the job as quoted if we need to refer
back to it.

Another potential issue is that some projects will involve pulling numbers
from 2 different summary spreadsheets, which are in 2 different folders. I
don't yet know what the name of the 2nd summary spreadsheet is. However, if
it's named the same as the first (but in a different path, such as
\screws\A.xls and \nails\A.xls), the current method has no way to specify
which of the Workbook A's to pull from, will just pull from whichever is open
at the time. I can work around this by coding the path into each cell, but
then I need to make sure everyone is mapped the same (since I never solved
the issue of not being able to use the UNC in the indirect function).

That stil leaves the issue of saving the values into the cells instead of
saving the formula when the workbook is saved. I had a thought that I could
create a hidden version of the summary worksheet, where I actually have all
the formulas, then have a macro which would use copy/paste special/value from
the hidden worksheet to the visible one. If I did this, would it just be a
huge macro with a line in the macro for each cell needing a value? Or is
there some other way to do this? Can I attach the macro to only certain cells
in a worksheet? And what would be the most user-proof way to make sure this
macro ran after all the values were updated in the hidden worksheet?

 

[Dave Peterson]

If I were going to have a macro that converted formulas to values, I'd have the
same macro do all the work. I'd have it build the formulas, populate the cells
and convert the formulas to values.

I don't have guess how intricate the macro would be. But if your data is laid
out nicely, it could be as straightforward as filling the range with a single
formula. If you write the formula nicely, then excel will adjust it just like
it does when you do it manually--select a range, write the formula for the
activecell and hit control-enter to fill all the cells in the range.