Use a cell value as part of a formula

[M@DG33K]

Hi All,

I'm trying to use a cell value as part of a vlookup function. The idea
is to keep changing the lookup query based on a the contents of a
specific cell

This is the forumla I'm currently using

VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[025-MainPub-ET-1801.XLS]Sheet1'!$A$1:$M$200,2,FALSE)

I need a way to replace "025-MainPub-ET-1801.XLS" in the formula based
on another cell, where this will be generated (or manually entered).

Right now I'm doing a select all, find and replace to achieve this.

Any help will be appreciated...

Chirag

 

[macropod]

Hi Chirag,

You could use the 'INDIRECT' function for this, but it will only work
correctly when the source workbook is open.

A workable alternative might be to use a macro to incorporate the cell's
string into the formula, for example:

Sub UpdateRef()
ActiveCell.Formula = "=VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[" & _
Range("A1").Value & "]Sheet1'!$A$1:$M$200,2,FALSE)"
End Sub

where the workbookname is in A1. You would probably also want to replace
'ActiveCell' with a specific cell's address.

Cheers

Hi All,

I'm trying to use a cell value as part of a vlookup function. The idea
is to keep changing the lookup query based on a the contents of a
specific cell

This is the forumla I'm currently using

VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[025-MainPub-ET-1801.XLS]Sheet1'!$A$1:$M
$200,2,FALSE)

I need a way to replace "025-MainPub-ET-1801.XLS" in the formula based
on another cell, where this will be generated (or manually entered).

Right now I'm doing a select all, find and replace to achieve this.

Any help will be appreciated...

Chirag

 

[M@DG33K]

Hey! thanks that worked perfectly.

What changed would I need to make to do it this in a range of cells? e.g c2:V11

Chirag

 

[Leo Heuser]

Chirag

It's not clear to me, if you want the same
formula in all cells. Is this, what you're after?

Sub UpdateRef()
Range("C2:V11").Formula = "=VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[" & _
Range("A1").Value & "]Sheet1'!$A$1:$M$200,2,FALSE)"
End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hey! thanks that worked perfectly.

What changed would I need to make to do it this in a range of cells? e.g c2:V11

Chirag


"macropod" <[email protected] (del NO.SPAM)> wrote in message

Hi Chirag,

You could use the 'INDIRECT' function for this, but it will only work
correctly when the source workbook is open.

A workable alternative might be to use a macro to incorporate the cell's
string into the formula, for example:

Sub UpdateRef()
ActiveCell.Formula = "=VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[" & _
Range("A1").Value & "]Sheet1'!$A$1:$M$200,2,FALSE)"
End Sub

where the workbookname is in A1. You would probably also want to replace
'ActiveCell' with a specific cell's address.

Cheers

 

[M@DG33K]

Hi Leo,

I experimented and used the same solution for a range. however it
doesn't solve the problem. because I need the formula to change
according to another variable (apart from the one used before).

The "07-Feb03"in the formula needs to change basis another cell that
will be in the same column as the formula

If this is a sample worksheet

Aug-02 Sep-02 Oct-02 Nov-02

TOTAL REVENUE 1294.41 1223.3 1531.23 1059.69
Advertisement Total 1052.65 963.69 1260.24 1000.1
Circulation Total 200.56 230.12 208.33 194.93
DVC - Print Media 784.37 741.74 862.85 689.33
Paper 700.3 649.31 762.74 587.63

Now each of the above is calculated basis links into a separate
workbook using the VBA snippet. I need the 07-Feb03 to change basis
the month on top of the column.

I tried modifying the VBA snippet to do it, but I couldn't figure out
how to keep the cell reference to keep changing. The solution that you
specified, to work needs to this change in the original formula...

Thanks in advance

Chirag

 

[Leo Heuser]

Hi Chirag

Still not 100% sure about the setup, but
you may try this snippet. I have assumed,
that Aug-02 Sep-02 Oct-02 Nov-02 etc.
is in C1:V1. I'm not sure about the "07-"-part
Maybe
"=VLOOKUP($A2,'C:\Matrix0203\" & TopText & "\["
should be
"=VLOOKUP($A2,'C:\Matrix0203\07-" & TopText & "\["
?

Sub UpdateRef()
Dim Cell As Range
Dim FormulaRange As Range
Dim TopText As String

With Sheets("Sheet1")
Set FormulaRange = .Range("C2:V11")

For Each Cell In FormulaRange.Cells
TopText = _
.Cells(FormulaRange.Offset(-1, 0).Row, Cell.Column).Value
Cell.Formula = _
"=VLOOKUP($A2,'C:\Matrix0203\" & TopText & "\[" & _
Range("A1").Value & "]Sheet1'!$A$1:$M$200,2,FALSE)"
Next Cell
End With
End Sub

 

[M@DG33K]

HI,

This is perfect...

This is how I'm using it

Sub UpdateR()
Dim Cell As Range
Dim FormulaRange As Range
Dim TopText As String


With Sheets("DM") ' My Sheet Name is DM
Set FormulaRange = .Range("d3:v4")

For Each Cell In FormulaRange.Cells
TopText = _
.Cells(FormulaRange.Offset(-2, 0).Row, Cell.Column).Value
Cell.Formula = _
"=VLOOKUP($A3,'" & TopText & "\[" & _
' Changed the forumla to keep the entire path in TopText
Range("c18").Value & "]Sheet1'!$A$1:$M$200,2,FALSE)"
Next Cell
End With
End Sub


The lookup cell in the vlookup needed to change rows as well

from A3 to A4 and so on...

There may be amore efficient solution, but this is how I solved it...


Sub UpdateR()
Dim Cell As Range
Dim FormulaRange As Range
Dim TopText As String
Dim SideText As String 'New Variable

'Worksheets("DataMiner").Activate
' Worksheets("DataMiner").Range("D3").Activate

With Sheets("DM")
Set FormulaRange = .Range("d3:v12")

For Each Cell In FormulaRange.Cells
TopText = _
.Cells(FormulaRange.Offset(-2, 0).Row, Cell.Column).Value
SideText = Cell.Row 'Added this
Cell.Formula = _
"=VLOOKUP($A" & SideText & " ,'" & TopText & "\[" & _

'Changed the Formula by dynamically adding row number
Range("c18").Value & "]Sheet1'!$A$1:$M$200,2,FALSE)"
Next Cell
End With
End Sub

Warm regards and Many many Thanks

Chirag

 

[Leo Heuser]

You're welcome, Chirag.
Glad you could use it, and thanks
for the feedback.

Regards
LeoH