VLookup Formula's

[willemeulen]

On sheet 2 I have a table with formula's, these formula's are written in the
Row style so that it will work on sheet 1. The lookup table has about 50
options.

Sheet 1:

Depending on the code in column C (example) I want excel to retrieve the
formula from the table in sheet 2 (not the result) in column A. I suspect i
would need VBA to achieve this and use vlookup for selecting correct formula.

Does anybody have an example of such a VBA?
In this case the trigger would be updating the value's in column C

W

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[JLatham]

There's not quite enough information here to do much more than give you a
general and incomplete solution.

Use Sheet1's _Change() event to trigger off of. A kind of general process
would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws2 As Worksheet
Dim luTable As Range
Dim FoundFormulaCell As Range

If Target.Column <> 3 Then
'not in column C, do nothing
Exit Sub
End If
'get ready to examine the table on Sheet2
Set ws2 = Worksheets("Sheet2")
'set a reference to the table of formulas
'on sheet
'change address as appropriate
Set luTable = ws2.Range("A1:R100")
'here's where I'm lost, and can't help
'because I don't know how you're using
'the entry in column C to determine
'what formula to return from the table
'but your column C entry can be
'referenced as 'Target'
'
'once you decide which formula from
'Sheet2 to use, then use something
'like this to copy the formula into
'column A
'assumes cell on Sheet2 is referenced
'by a variable named FoundFormulaCell
Range("A" & Target.Row).FormulaR1C1 = _
FoundFormulaCell.FormulaR1C1

Set ws2 = Nothing ' good housekeeping
End Sub