join location string with variable
 

Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!e$5

Thank you very much

join location string with variable
 

sFirst = "='C:\locations\transfer\["
sLast = ".xls]Sheet3'!e$5"

set cell = Range("A1")
do while not isempty(cell)
cell.offset(0,1).formula = sFirst & cell.value & sLast
set cell = cell.offset(1,0)
Loop

--
Regards,
Tom Ogilvy

igor wrote in message
...
Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!e$5

Thank you very much

join location string with variable
 

First concatenate the text values correctly
A1 value is: abc
B1 value is: c:\locations\transfer\

so C1 =A1 & "[" & B1 & "]sheet3'!E5"

th evalue in C1 should look like the correct formula for
pointing at the cell. I think you;'re still m,issing a
leading single quote.
Now use the INDIRECT function to get th edata out of the
workbopok

D1 = Indirect(C1)


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each
file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!e$5

Thank you very much

.

join location string with variable
 

You have A1 and B1 reversed between what values are stored and your formula
and you left out the leading single quote in B1 - regardless, Indirect
doesn't work with a closed workbook which is the format of the linking
formula shown.

--
Regards,
Tom Ogilvy

"Patrick Molloy" wrote in message
...
First concatenate the text values correctly
A1 value is: abc
B1 value is: c:\locations\transfer\

so C1 =A1 & "[" & B1 & "]sheet3'!E5"

th evalue in C1 should look like the correct formula for
pointing at the cell. I think you;'re still m,issing a
leading single quote.
Now use the INDIRECT function to get th edata out of the
workbopok

D1 = Indirect(C1)


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each
file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!e$5

Thank you very much

.

join location string with variable
 

I posted a VBA solution ~3.75 hrs after you posted the question. You
haven't said why that was rejected.

--
Regards,
Tom Ogilvy

Igor wrote in message
...
Tom,
you are correct, that suggestion helped with thinking but
did not resolve the problem. Any ideas on how to do it??

Thank you to all

-----Original Message-----
You have A1 and B1 reversed between what values are
stored and your formula
and you left out the leading single quote in B1 -
regardless, Indirect
doesn't work with a closed workbook which is the format
of the linking
formula shown.

--
Regards,
Tom Ogilvy

"Patrick Molloy" wrote in
message
...
First concatenate the text values correctly
A1 value is: abc
B1 value is: c:\locations\transfer\

so C1 =A1 & "[" & B1 & "]sheet3'!E5"

th evalue in C1 should look like the correct formula for
pointing at the cell. I think you;'re still m,issing a
leading single quote.
Now use the INDIRECT function to get th edata out of the
workbopok

D1 = Indirect(C1)


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,

I'm having trouble connecting file location with a file
name. here is what i mean:
In the column A i have file names listed as

aba
abb
abc

these files are located on c:\locations\transfer
i'm trying to get to the Sheet3 and cell E5 on each
file.
So at the end i should have
A B
aba value of c:\locations\transfer\[ABA.xls]Sheet3'!
e$5
abb value of c:\locations\transfer\[ABB.xls]Sheet3'!
e$5
abc value of c:\locations\transfer\[ABC.xls]Sheet3'!
e$5

Thank you very much

.



.