Here's the simple formula:

=((VLOOKUP($A3,'C:\Documents and Settings\mkline01\Desktop\New Folder
\[36.xls]Sheet1'!$3:$16,2,FALSE)

I need to replace the 36 from [36.xls] with the value from an adjacent
cell. This will simplify copying this formula over many cells.

There's probably a simply soultion, I just can't find it.

Mark

To do this with built in functions would *require* that the source file be
open which usually isn't very desireable.

Here's a link to site with an add-in that has a UDF that might do what you
want:

http://xcell05.free.fr/

Look for INDIRECT.EXT

I don't have this add-in but I've seen it mentioned here often.

Biff

"cyberbrewer" wrote in message
oups.com...
Here's the simple formula:

=((VLOOKUP($A3,'C:\Documents and Settings\mkline01\Desktop\New Folder
\[36.xls]Sheet1'!$3:$16,2,FALSE)

I need to replace the 36 from [36.xls] with the value from an adjacent
cell. This will simplify copying this formula over many cells.

There's probably a simply soultion, I just can't find it.

Mark

Hi Mark:

There is not a simple solution to this problem.

The easiest solution is to use INDIRECT but this does not work with closed
workbooks as in:

=((VLOOKUP($A3,indirect("'["& B2 & "]Sheet1'!$3:$16"),2,FALSE)

The other way is to use a worksheet change macro to pick up the change in
the cell

On the sheet tab at the bottom click view code and then paste the following
in.
changing C1 to the cell sheet the worksheet name is.

It also assumes that there is only one [ and ] in the formula for the vlookup.

Option Explicit
Const cszCell As String = "C1" ' cell with the worksheet in
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String, s1 As String
If Not (Intersect(Range(cszCell), Target) Is Nothing) Then
With Range(cszCell).Offset(0, -1)
s = .Formula
s1 = Left(s, InStr(1, s, "["))
s1 = s1 & .Offset(0, 1)
s1 = s1 & Right(s, InStr(StrReverse(s), "]"))
.Formula = s1
End With
End If
End Sub
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"cyberbrewer" wrote:

Here's the simple formula:

=((VLOOKUP($A3,'C:\Documents and Settings\mkline01\Desktop\New Folder
\[36.xls]Sheet1'!$3:$16,2,FALSE)

I need to replace the 36 from [36.xls] with the value from an adjacent
cell. This will simplify copying this formula over many cells.

There's probably a simply soultion, I just can't find it.

Mark

Thanks very much for the info. The linked workbooks will be closed,
is ther any way to do this without adding macros? If I do add macros
is it possible to paste the formula accross many cells?

Mark


On Feb 11, 6:48 pm, "cyberbrewer" wrote:
Here's the simple formula:

=((VLOOKUP($A3,'C:\Documents and Settings\mkline01\Desktop\New Folder
\[36.xls]Sheet1'!$3:$16,2,FALSE)

I need to replace the 36 from [36.xls] with the value from an adjacent
cell. This will simplify copying this formula over many cells.

There's probably a simply soultion, I just can't find it.

Mark

Mark you can change the link to the cells and modify the code a little

Option Explicit

Const cszCell As String = "C1, C10:C20" ' change this to all the cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String, s1 As String
dim rCell as range
for each rCell in target
If Not (Intersect(Range(cszCell), rCell) Is Nothing) Then
With rcell.Offset(0, -1)
s = .Formula
s1 = Left(s, InStr(1, s, "["))
s1 = s1 & .Offset(0, 1)
s1 = s1 & Right(s, InStr(StrReverse(s), "]"))
.Formula = s1
End With
End If
next rCell
End Sub



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"cyberbrewer" wrote:

Thanks very much for the info. The linked workbooks will be closed,
is ther any way to do this without adding macros? If I do add macros
is it possible to paste the formula accross many cells?

Mark


On Feb 11, 6:48 pm, "cyberbrewer" wrote:
Here's the simple formula:

=((VLOOKUP($A3,'C:\Documents and Settings\mkline01\Desktop\New Folder
\[36.xls]Sheet1'!$3:$16,2,FALSE)

I need to replace the 36 from [36.xls] with the value from an adjacent
cell. This will simplify copying this formula over many cells.

There's probably a simply soultion, I just can't find it.

Mark