Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
Maybe some more info would help. Are you sure you don't want to find
and replace? 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
Yep - you've basically answered it. I'm looking to over write it
without acually going to the cell. 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
mjj047s wrote: THEIR IS? Sure. The most obvious way is a macro. 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple VLookup Question
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
simple vlookup | Excel Worksheet Functions | |||
I need help with a simple vlookup formula | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
simple question, hopefully a simple answer! | Excel Programming |