About Us

mjj047s

awesome - thanks so much! have a great weekend guys.

wrote:
One more note. Just type over the vlookup formula in C2 to replace the
value in its original location.

wrote:
Place the following in a general module. Lookup value is entered on
sheet 1, B2. Vlookup is created in C2. Address of found value is stored
in D2. Table is the lookup table on sheet2. Table1 is a single column
range of the search column in the vlookup

Sub Auto_Open()
Application.Calculation = xlCalculationAutomatic
Sheets("Sheet1").OnEntry = "Auto_Routines"
End Sub
Sub Auto_Routines()
Enter_Lookup
Replace_Value
End Sub

Sub Enter_Lookup() Create vlookup and cell address of found value.
If ActiveCell.Address = "$B$2" Then
ActiveCell.Offset(0, 1).Formula = "=VLOOKUP(B2,Table,2,FALSE)"
ActiveCell.Offset(0, 2).Formula = _
"=CELL(""Address"",INDEX(Table1,MATCH(C2,table1),1 ))"
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value
Else
Exit Sub
End If
End Sub

Sub Replace_Value() 'Replaces vlookup formula with replacement value.
Dim MyAddress As String
If ActiveCell.Address < "$C$2" Then
Exit Sub
Else
MyAddress = ActiveSheet.Range("D2").Value
Range(MyAddress).Value = Range("C2").Value
MyAddress = Empty
End If
End Sub

mjj047s wrote:
THEIR IS?

davegb wrote:
mjj047s wrote:
Yep - you've basically answered it. I'm looking to over write it
without acually going to the cell.

There's lots of ways to do that.

davegb wrote:
mjj047s wrote:
Can I do a Vlookup for a set of data, and change the return data?
For example - When i type in "Apples" in A1, "Red" in returned in A2
Can I manually type "Green" into A2 or somewhere to have it change from
where it was pulling from????

If i need to explain more, please let me know.

If I'm interpreting you correctly, you have a vlookup table with Apples
in say C1, Red in D1, Oranges in C2, Orange in D2, etc. When you enter
Apples in A1, the vlookup function in A2 finds Apples in A1 and returns
Red. You want to know if, by changing A2 to Green, it will change the
table value in D1 to Green. The answer is no. Doesn't work in reverse.
You'd simply overwrite your formula.

Maybe if you give some more detail, we can help you solve the problem
some other way?

Hope this helps in your world.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IF formula-simple question; simple operator	Rich D	Excel Discussion (Misc queries)	4	December 6th 07 03:36 PM
simple vlookup	vertigo	Excel Worksheet Functions	5	April 20th 07 11:13 AM
I need help with a simple vlookup formula	phjohnso	Excel Worksheet Functions	3	August 13th 06 01:59 PM
Simple Simple Excel usage question	BookerW	Excel Discussion (Misc queries)	1	June 23rd 05 10:06 PM
simple question, hopefully a simple answer!	Matt B	Excel Programming	5	January 13th 04 08:43 PM

Menu

About Us