Help: Using text from an adjacent cell in a vlookup

[cyberbrewer]

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

 

[T. Valko]

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

 

[Martin Fishlock]

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

 

[cyberbrewer]

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

 

[Martin Fishlock]

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.

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

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