Link to workbook using nominated cell text referencing

[108monkeys]

Hello,

I am wanting to link to a seperate workbook cell using the formulae ='[INF_Cap_8400-8051.xls]Infra Cap'!$C45 BUT instead of using the workbook to link to the cells of interest I would like to use text in Cell B1 for example.

<TABLE style="WIDTH: 127pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=170 border=0 x:str><COLGROUP><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 17pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=23 height=17>

A​

</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 110pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=147>

B​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>

1​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

INF_Cap_8400-8051.xls​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>

2​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39919">

16-Apr-09​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>

3​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

Add​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>

4​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

1​

</TD></TR></TBODY></TABLE>

So I unsuccessully tried someting like this:
='[B1]Infra Cap '!$C45

What do I need to add to this or an alternative method to link other work book cells by passing nominated text. I would like to copy these columns and stop waisting time opening static workbooks.

Hope you can assist.

 

[Weaver]

The problem is you're after looking in other workbooks, so the INDIRECT function won't work. How and when would this reference be likely to change?

 

[108monkeys]

(Man you are fast!)

Well, when I copy and past B:B into the new C:C I will rename cell C1 to INF_Cap_8400-8051.xls for example and will then access the required cell.

Cell C4 will now link to the new workbook cell. Hence the only cells which will change are ROW 1:1 and the other workbook's cell information.

 

[108monkeys]

Can anyone else help me with this question?

I am trying to do a cell lookup between two workbooks while passing cell inforation to build a link cells. I am thinking something similar to the following HYPERLINK command:

=IF(K2<>"NO",HYPERLINK("C:\TEMP\"&E102&"_"&VLOOKUP(F:F,STREET_LOOKUP!$A$1:$B$69,2,FALSE)),"NOT AVAILABLE")

This would save lots of time instead of opening and linking to each workbook and cell. From the original post C1 would refer to a different workbook and C2 to C4 would be reading from specific cells in that workbook...

Hope this is possible.