Assembling external cell reference to display value from other workbooks

[veni]

Ok, now that I shocked you with an indiscriptive title but still have your attention, let me see if I can describe that better:

In one cell I have a reference to an external workbook, e.g. B1: \\server\path\filename.xls. In another cell (eg. B3) I would like to use that info add the sheet name and the row/column reference to it. Alternatively, the sheet name could be in a separate cell (e.g. B2).

In effect it is like what I can do in one step ('\\server\path\[filename.xls]Data'!X25) in two or three steps. This allows me to separate out the file I'm referring to, the sheet and the cell, so that when I need to change the file name, but sheet and cell reference stay the same, I would still end up with the right value.
<TABLE cellSpacing=0 cellPadding=0 border=1 noshade>

<TBODY><TR><TD></TD><TD>A</TD><TD>B</TD></TR>

​

<TR><TD>1 </TD><TD>file name</TD><TD>\\server\path\filename.xls</TD></TR>

​

<TR><TD>2</TD><TD>sheet</TD><TD>Data</TD></TR>

​

​

<TR><TD>3 </TD><TD>Info from other file's 'Data' sheet in X25

​

</TD><TD>how do I assemble the cell formula here?</TD></TR>



​

</TABLE>

 

[ravishankar]

Hi
Let A1=(Path) '\\server\path\
B1= filename.xls you can change it to suit your needs.
C1= Data'!X25

sub pull data
Cells(1,4) = cells(1,1) & cells(1,2) & cells(1,3)
end sub

ravi

 

[veni]

Thanks, but this didn't really produce anything usable. In fact, after setting pull_data (I assume it had to have a "_" in its name), I have to run a macro first to populate the field, and then all it did was to enter

\\server\path\filename.xlsData'!X25

in cell D1. I made these modifications to the VBA code:

Sub pull_data()
    Cells(1, 4) = "='" & Cells(1, 1) & "[" & Cells(1, 2) & "]" & Cells(1, 3)
End Sub

This produces the desired result in cell, but I was looking for something that wouldn't require VBA and can be used in situ. Purely for the reason that if the location of the resulting cell changes I would have to change the code and I would have to run a macro every time I would want to update the cells.

I was looking for something like

="'" & A1 & "[" & B1 & "]" & C1

as the cell formula. It's just that this doesn't produce the correct output. Is there a function that evaluates a string?

 

[veni]

Does anyone have a solution for this?