| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| retrieve an old file erased by saving a new file with same name | Excel Discussion (Misc queries) | |||
| retrieve a saved over file | Excel Discussion (Misc queries) | |||
| How do I retrieve a saved over file? | Excel Discussion (Misc queries) | |||
| If I accidently replace a saved file can I retrieve the old file? | Excel Discussion (Misc queries) | |||
| Anyone know how to retrieve a deleted file (file has a name, comp. | New Users to Excel | |||
Linear Mode
