Hi, I hope you can help....
If I go through the long process and select
='C:\Documents and Settings\me\My Documents\BNS\BNS20060002.xls'!Invoice_date
it will return the value of the cell
but....
when B11= 'C:\Documents and Settings\me\My Documents\BNS\
when A27= BNS20060002
When B20= Invoice_date
I write
=CONCATENATE(B11,A27,"xls'!",B20)
It creates
='C:\Documents and Settings\me\My Documents\BNS\BNS20060002.xls'!Invoice_date
seemingly as text, how do I get it to be a cell reference?
I need to build this reference automatically.

Normally you would indirect the concatenated test to get the cell value, but
that won't work for closed workbooks, so take a look at

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sbardon" wrote in message
...
Hi, I hope you can help....
If I go through the long process and select
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
it will return the value of the cell
but....
when B11= 'C:\Documents and Settings\me\My Documents\BNS\
when A27= BNS20060002
When B20= Invoice_date
I write
=CONCATENATE(B11,A27,"xls'!",B20)
It creates
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
seemingly as text, how do I get it to be a cell reference?
I need to build this reference automatically.

Mr Philips,
I downloaded and then tried the tool you suggested, without any luck.

I am wondeing, as it excel regularly works when I select name cell in an
open workbook, and it functions later if the data is changed, even when the
reference work book is closed. I can get the function to work I just cannot
auto build the function.
The character string in the cell looks Identical...
could it be somekind of formatting thing?
thanks,Stephen


"Bob Phillips" wrote:

Normally you would indirect the concatenated test to get the cell value, but
that won't work for closed workbooks, so take a look at

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sbardon" wrote in message
...
Hi, I hope you can help....
If I go through the long process and select
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
it will return the value of the cell
but....
when B11= 'C:\Documents and Settings\me\My Documents\BNS\
when A27= BNS20060002
When B20= Invoice_date
I write
=CONCATENATE(B11,A27,"xls'!",B20)
It creates
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
seemingly as text, how do I get it to be a cell reference?
I need to build this reference automatically.

I don't think it is anything to do with formatting.

I have never used the utility myself, I never link to closed workbooks, but
many people recommend it, and Laurent knows what he is doing, so I assumed
it was good. What was the command as issued by you?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sbardon" wrote in message
...
Mr Philips,
I downloaded and then tried the tool you suggested, without any luck.

I am wondeing, as it excel regularly works when I select name cell in an
open workbook, and it functions later if the data is changed, even when
the
reference work book is closed. I can get the function to work I just
cannot
auto build the function.
The character string in the cell looks Identical...
could it be somekind of formatting thing?
thanks,Stephen


"Bob Phillips" wrote:

Normally you would indirect the concatenated test to get the cell value,
but
that won't work for closed workbooks, so take a look at

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sbardon" wrote in message
...
Hi, I hope you can help....
If I go through the long process and select
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
it will return the value of the cell
but....
when B11= 'C:\Documents and Settings\me\My Documents\BNS\
when A27= BNS20060002
When B20= Invoice_date
I write
=CONCATENATE(B11,A27,"xls'!",B20)
It creates
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
seemingly as text, how do I get it to be a cell reference?
I need to build this reference automatically.

Mr Phillips,
Where
B10 = €œ[
A28 = BNS20060002
C10 = .xls]
D10 =costing
E10 =!
B20 = Invoice_date
F10 =€�

I tried
=INDIRECT.EXT(B9)
where B9 = =CONCATENATE(B10,A28,C10,D10,E10,B20,F10)
and displayed "[BNS20060002.xls]Costing!Invoice_date"
~did not work #VALUE!

=INDIRECT.EXT((CONCATENATE(B10,A28,C10,D10,E10,B20 ,F10)))
~did not work #VALUE!

if I type in a fresh cell...
=INDIRECT.EXT("[BNS20060002.xls]Costing!Invoice_date")
~ this does work... correct answer!

I can get the function to work, but I cannot manage to automatically write
a function in a cell and have it return the answer?? This is my issue€¦

Regards
Stephen Bardon


"Bob Phillips" wrote:

I don't think it is anything to do with formatting.

I have never used the utility myself, I never link to closed workbooks, but
many people recommend it, and Laurent knows what he is doing, so I assumed
it was good. What was the command as issued by you?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sbardon" wrote in message
...
Mr Philips,
I downloaded and then tried the tool you suggested, without any luck.

I am wondeing, as it excel regularly works when I select name cell in an
open workbook, and it functions later if the data is changed, even when
the
reference work book is closed. I can get the function to work I just
cannot
auto build the function.
The character string in the cell looks Identical...
could it be somekind of formatting thing?
thanks,Stephen


"Bob Phillips" wrote:

Normally you would indirect the concatenated test to get the cell value,
but
that won't work for closed workbooks, so take a look at

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from


http://xcell05.free.fr/english/


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sbardon" wrote in message
...
Hi, I hope you can help....
If I go through the long process and select
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
it will return the value of the cell
but....
when B11= 'C:\Documents and Settings\me\My Documents\BNS\
when A27= BNS20060002
When B20= Invoice_date
I write
=CONCATENATE(B11,A27,"xls'!",B20)
It creates
='C:\Documents and Settings\me\My
Documents\BNS\BNS20060002.xls'!Invoice_date
seemingly as text, how do I get it to be a cell reference?
I need to build this reference automatically.