retrieve value in file (NO VBA)
 

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

retrieve value in file (NO VBA)
 

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" 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

retrieve value in file (NO VBA)
 

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.)

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" 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

retrieve value in file (NO VBA)
 

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

Dave Peterson wrote:

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.)

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" 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

retrieve value in file (NO VBA)
 

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" 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

retrieve value in file (NO VBA)
 

replace it with an equal sign. (=).



Mire wrote:

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" 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

retrieve value in file (NO VBA)
 

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!)

Dave Peterson wrote:

replace it with an equal sign. (=).

Mire wrote:

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" 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


--

Dave Peterson

retrieve value in file (NO VBA)
 

Tnx Dave, it now works (great!)



M.


"Dave Peterson" ha scritto nel messaggio
...
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!)

Dave Peterson wrote:

replace it with an equal sign. (=).

Mire wrote:

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" 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


--

Dave Peterson