retrieve value in file (NO VBA)

[Mire]

Hi,
In cell A1 of summary.xls, I need to retrieve the value of cells A1 of the
file with name in cells B1 of summary.xls.

Ex.

In A1 of summary.xls
='C:\[a.xls]Foglio1'!$A$1

I need to insert the namefile in B1 of summary.xls
---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1

Thanks in advance to anyone
M

 

[Mire]

If in A1 i put

=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the
filename to open "tmp1.xls")

this returns exactly the path for have the value in file tmp1 cell B1, but
the cell returns the text

'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1


not the value of the file !!

How can I retrive the value?

Tnx
M.

 

[Dave Peterson]

You'd really have to use =Indirect() for this. But the bad news is that
=Indirect() doesn't work with closed files.

But if your other workbooks were open, it would work!
=INDIRECT("'C:\a\aste\[" & B8 & "]foglio1'!$b$1")
(if the other workbooks are closed, you'll see #Ref! errors.)

An non-macro way if your workbooks are closed:

Build your formula like:
="$$$$"&"'C:\a\Aste\["& B8 &".xls]"&"Foglio1'!$B$1"

Let it/them evaluate to
$$$$'C:\a\Aste\[book1.xls]Foglio1'!$B$1
Now select that range of cells and
Edit|Replace
find what: $$$$
replace with: =

And your text strings will be converted to formulas and the formulas will try to
retrieve that data from the closed workbook. (Typing mistakes are a big problem
(when it tries to find the "sending" workbook/worksheet). So be careful.)

If in A1 i put

=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the
filename to open "tmp1.xls")

this returns exactly the path for have the value in file tmp1 cell B1, but
the cell returns the text

'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1

not the value of the file !!

How can I retrive the value?

Tnx
M.

Hi,
In cell A1 of summary.xls, I need to retrieve the value of cells A1 of the
file with name in cells B1 of summary.xls.

Ex.

In A1 of summary.xls
='C:\[a.xls]Foglio1'!$A$1

I need to insert the namefile in B1 of summary.xls
---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1

Thanks in advance to anyone
M

 

[Dave Peterson]

But if your other workbook is already open, you may not want the drive and
folder included in your formula.

You'd really have to use =Indirect() for this. But the bad news is that
=Indirect() doesn't work with closed files.

But if your other workbooks were open, it would work!
=INDIRECT("'C:\a\aste\[" & B8 & "]foglio1'!$b$1")
(if the other workbooks are closed, you'll see #Ref! errors.)

An non-macro way if your workbooks are closed:

Build your formula like:
="$$$$"&"'C:\a\Aste\["& B8 &".xls]"&"Foglio1'!$B$1"

Let it/them evaluate to
$$$$'C:\a\Aste\[book1.xls]Foglio1'!$B$1
Now select that range of cells and
Edit|Replace
find what: $$$$
replace with: =

And your text strings will be converted to formulas and the formulas will try to
retrieve that data from the closed workbook. (Typing mistakes are a big problem
(when it tries to find the "sending" workbook/worksheet). So be careful.)

If in A1 i put

=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the
filename to open "tmp1.xls")

this returns exactly the path for have the value in file tmp1 cell B1, but
the cell returns the text

'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1

not the value of the file !!

How can I retrive the value?

Tnx
M.

Hi,
In cell A1 of summary.xls, I need to retrieve the value of cells A1 of the
file with name in cells B1 of summary.xls.

Ex.

In A1 of summary.xls
='C:\[a.xls]Foglio1'!$A$1

I need to insert the namefile in B1 of summary.xls
---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1

Thanks in advance to anyone
M

 

[Mire]

Hi Dave.

With find/replace doesn't work.

After replace ("$$$$") A1 appears as:

'C:\a\Aste\[ABA104.xls]Foglio1'!$C$2 ->
(=""&("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$C$2"))

and it doesn't retrieve the value

Tnx

M.

Build your formula like:

="$$$$"&"'C:\a\Aste\["& B8 &".xls]"&"Foglio1'!$B$1"
Let it/them evaluate to

$$$$'C:\a\Aste\[book1.xls]Foglio1'!$B$1

Now select that range of cells and

Edit|Replace

find what: $$$$

replace with: =

And your text strings will be converted to formulas and the formulas will

try to

retrieve that data from the closed workbook. (Typing mistakes are a big problem

(when it tries to find the "sending" workbook/worksheet). So be careful.)
Mire wrote:

If in A1 i put

=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the
filename to open "tmp1.xls")

this returns exactly the path for have the value in file tmp1 cell B1,

but

the cell returns the text

'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1

not the value of the file !!

How can I retrive the value?

Tnx
M.

"Mire" <[email protected]> ha scritto nel messaggio

Hi,
In cell A1 of summary.xls, I need to retrieve the value of cells A1 of

the

file with name in cells B1 of summary.xls.

Ex.

In A1 of summary.xls
='C:\[a.xls]Foglio1'!$A$1

I need to insert the namefile in B1 of summary.xls
---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1

Thanks in advance to anyone
M

--

Dave Peterson

 

[Dave Peterson]

replace it with an equal sign. (=).

Hi Dave.

With find/replace doesn't work.

After replace ("$$$$") A1 appears as:

'C:\a\Aste\[ABA104.xls]Foglio1'!$C$2 ->
(=""&("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$C$2"))

and it doesn't retrieve the value

Tnx

M.

Build your formula like:

="$$$$"&"'C:\a\Aste\["& B8 &".xls]"&"Foglio1'!$B$1"
Let it/them evaluate to

$$$$'C:\a\Aste\[book1.xls]Foglio1'!$B$1

Now select that range of cells and

Edit|Replace

find what: $$$$

replace with: =

And your text strings will be converted to formulas and the formulas will

try to

retrieve that data from the closed workbook. (Typing mistakes are a big problem

(when it tries to find the "sending" workbook/worksheet). So be careful.)
Mire wrote:

If in A1 i put

=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the
filename to open "tmp1.xls")

this returns exactly the path for have the value in file tmp1 cell B1,

but

the cell returns the text

'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1

not the value of the file !!

How can I retrive the value?

Tnx
M.

"Mire" <[email protected]> ha scritto nel messaggio

Hi,
In cell A1 of summary.xls, I need to retrieve the value of cells A1 of

the

file with name in cells B1 of summary.xls.

Ex.

In A1 of summary.xls
='C:\[a.xls]Foglio1'!$A$1

I need to insert the namefile in B1 of summary.xls
---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1

Thanks in advance to anyone
M

--

Dave Peterson

(e-mail address removed)

 

[Dave Peterson]

Ooh. I left out a step before you do the Edit|replace. (Sorry)

Make sure you convert it to a value (copy|Paste special|Values), too. (Now it's
just text.)

Then edit|replace.

(sorry again!)

replace it with an equal sign. (=).

Hi Dave.

With find/replace doesn't work.

After replace ("$$$$") A1 appears as:

'C:\a\Aste\[ABA104.xls]Foglio1'!$C$2 ->
(=""&("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$C$2"))

and it doesn't retrieve the value

Tnx

M.

Build your formula like:

="$$$$"&"'C:\a\Aste\["& B8 &".xls]"&"Foglio1'!$B$1"
Let it/them evaluate to

$$$$'C:\a\Aste\[book1.xls]Foglio1'!$B$1

Now select that range of cells and

Edit|Replace

find what: $$$$

replace with: =

And your text strings will be converted to formulas and the formulas will

try to

retrieve that data from the closed workbook. (Typing mistakes are a big problem

(when it tries to find the "sending" workbook/worksheet). So be careful.)
Mire wrote:
If in A1 i put
=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the

filename to open "tmp1.xls")
this returns exactly the path for have the value in file tmp1 cell B1, but

the cell returns the text

'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1

not the value of the file !!
How can I retrive the value?

Tnx

M.

"Mire" <[email protected]> ha scritto nel messaggio

news:[email protected]...

Hi,

In cell A1 of summary.xls, I need to retrieve the value of cells A1 of the

file with name in cells B1 of summary.xls.

Ex.

In A1 of summary.xls

='C:\[a.xls]Foglio1'!$A$1

I need to insert the namefile in B1 of summary.xls

---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1
Thanks in advance to anyone

M

--

Dave Peterson

(e-mail address removed)

 

[Mire]

Tnx Dave, it now works (great!)



M.

Ooh. I left out a step before you do the Edit|replace. (Sorry)

Make sure you convert it to a value (copy|Paste special|Values), too. (Now it's
just text.)

Then edit|replace.

(sorry again!)

replace it with an equal sign. (=).

Hi Dave.

With find/replace doesn't work.

After replace ("$$$$") A1 appears as:

'C:\a\Aste\[ABA104.xls]Foglio1'!$C$2 ->
(=""&("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$C$2"))

and it doesn't retrieve the value

Tnx

M.



Build your formula like:

="$$$$"&"'C:\a\Aste\["& B8 &".xls]"&"Foglio1'!$B$1"



Let it/them evaluate to

$$$$'C:\a\Aste\[book1.xls]Foglio1'!$B$1

Now select that range of cells and

Edit|Replace

find what: $$$$

replace with: =



And your text strings will be converted to formulas and the formulas will
try to

retrieve that data from the closed workbook. (Typing mistakes are a big
problem

(when it tries to find the "sending" workbook/worksheet). So be careful.)



Mire wrote:



If in A1 i put



=("'C:\a\Aste\["&(B8)&".xls]"&"Foglio1'!$B$1") (where B8 contains the

filename to open "tmp1.xls")



this returns exactly the path for have the value in file tmp1 cell B1,
but

the cell returns the text



'C:\a\Aste\[tmp1.xls]Foglio1'!$B$1



not the value of the file !!



How can I retrive the value?



Tnx

M.



"Mire" <[email protected]> ha scritto nel messaggio


Hi,

In cell A1 of summary.xls, I need to retrieve the value of cells A1 of
the

file with name in cells B1 of summary.xls.



Ex.



In A1 of summary.xls

='C:\[a.xls]Foglio1'!$A$1



I need to insert the namefile in B1 of summary.xls

---> 'C:\[ ** value of cells B1 ** ]Foglio1'!$A$1



Thanks in advance to anyone

M







--



Dave Peterson

(e-mail address removed)