Lookup

  • Thread starter Thread starter CarlosAntenna
  • Start date Start date
C

CarlosAntenna

I want to do a lookup without using the lookup function. Here is the idea:

I have a lookup table correlating Lot Numbers to Material Thickness.
In another sheet I want to enter a lot number into a cell and have it return
the corresponding material thickness to that same cell.
I don't want to have a column on the sheet for lot numbers to lookup using
the VLOOKUP() function.

Since I can't do it with a function, I think it requires some VBA
programming. Very likely, this has come up before. Can someone point me to
a solution?

-- Carlos
 
right click sheet tab>view code insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(Target, [lookuptable], 2, 0)
Application.EnableEvents = True
End Sub
 
I'm a bit confused. If you don't have a column for lot numbers, how is
your table "correlating" lot numbers to material thicknesses?
 
The lot numbers do indeed exist on the _lookup table_. Where I don't want a
column for lot numbers is on the other sheet where I am entering other data.
I want to type a lot number in the thickness column on this second sheet and
have the thickness returned from the lookup table.

-- Carlos
 
Don,
Thanks for your reply.
I have tried modifying your code to suit, but I have not been successful.
My lookup table is on sheetname "LookUp", columns A:B and is a named range
"lookupt".
The sheet where I want to return the data is sheetname "TestLog" columns F &
G, with different data going into each of the two columns beginning at row 4
thru 400.
Can you help me out with the translation?

My modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(active.cell, lookupt, 2, 0)
Application.EnableEvents = True
End Sub

I'm not sure what your second line is all about.

-- Carlos


Don Guillett said:
right click sheet tab>view code insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(Target, [lookuptable], 2, 0)
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
CarlosAntenna said:
I want to do a lookup without using the lookup function. Here is the idea:

I have a lookup table correlating Lot Numbers to Material Thickness.
In another sheet I want to enter a lot number into a cell and have it return
the corresponding material thickness to that same cell.
I don't want to have a column on the sheet for lot numbers to lookup using
the VLOOKUP() function.

Since I can't do it with a function, I think it requires some VBA
programming. Very likely, this has come up before. Can someone point
Click to expand...
me
to
a solution?

-- Carlos
Click to expand...
Click to expand...
 
Your words taken at face value indicated that you wanted to type a value
into ONE cell and have the lookup returned to THAT cell. So, I set it up to
ONLY work on cell A3.
I want to enter a lot number into a cell and have it return
the corresponding material thickness to that same cell.
Click to expand...


--
Don Guillett
SalesAid Software
(e-mail address removed)
CarlosAntenna said:
Don,
Thanks for your reply.
I have tried modifying your code to suit, but I have not been successful.
My lookup table is on sheetname "LookUp", columns A:B and is a named range
"lookupt".
The sheet where I want to return the data is sheetname "TestLog" columns F &
G, with different data going into each of the two columns beginning at row 4
thru 400.
Can you help me out with the translation?

My modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(active.cell, lookupt, 2, 0)
Application.EnableEvents = True
End Sub

I'm not sure what your second line is all about.

-- Carlos


Don Guillett said:
right click sheet tab>view code insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(Target, [lookuptable], 2, 0)
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
CarlosAntenna said:
I want to do a lookup without using the lookup function. Here is the idea:

I have a lookup table correlating Lot Numbers to Material Thickness.
In another sheet I want to enter a lot number into a cell and have it return
the corresponding material thickness to that same cell.
I don't want to have a column on the sheet for lot numbers to lookup using
the VLOOKUP() function.

Since I can't do it with a function, I think it requires some VBA
programming. Very likely, this has come up before. Can someone point
Click to expand...
me
to
a solution?

-- Carlos
Click to expand...
Click to expand...
Click to expand...
 
Sorry about that. It made sense when I wrote it, but it sounds different
when you read it back to me.


Don Guillett said:
Your words taken at face value indicated that you wanted to type a value
into ONE cell and have the lookup returned to THAT cell. So, I set it up to
ONLY work on cell A3.
I want to enter a lot number into a cell and have it return
the corresponding material thickness to that same cell.
Click to expand...


--
Don Guillett
SalesAid Software
(e-mail address removed)
CarlosAntenna said:
Don,
Thanks for your reply.
I have tried modifying your code to suit, but I have not been successful.
My lookup table is on sheetname "LookUp", columns A:B and is a named range
"lookupt".
The sheet where I want to return the data is sheetname "TestLog" columns
Click to expand...
F
&
G, with different data going into each of the two columns beginning at
Click to expand...
row
4
thru 400.
Can you help me out with the translation?

My modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(active.cell, lookupt, 2, 0)
Application.EnableEvents = True
End Sub

I'm not sure what your second line is all about.

-- Carlos


Don Guillett said:
right click sheet tab>view code insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(Target, [lookuptable], 2, 0)
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
I want to do a lookup without using the lookup function. Here is the
idea:

I have a lookup table correlating Lot Numbers to Material Thickness.
In another sheet I want to enter a lot number into a cell and have it
return
the corresponding material thickness to that same cell.
I don't want to have a column on the sheet for lot numbers to lookup using
the VLOOKUP() function.

Since I can't do it with a function, I think it requires some VBA
programming. Very likely, this has come up before. Can someone
Click to expand...
point
me
to
a solution?

-- Carlos
Click to expand...
Click to expand...
Click to expand...
 
<Attempt to clarify further>
Each row contains test data, two of the relevant pieces of data are plate
thicknesses of the top and bottom plate. But the plates are identified by
lot number rather than thickness. The lookup table correlates the lot
number with a thickness. So in each row of test data I want to type in the
lot number for the top plate in column F and for the bottom plate in column
G and have the corresponding thicknesses returned into the same cell where I
typed the lot number.

-- Carlos

Don Guillett said:
Your words taken at face value indicated that you wanted to type a value
into ONE cell and have the lookup returned to THAT cell. So, I set it up to
ONLY work on cell A3.
I want to enter a lot number into a cell and have it return
the corresponding material thickness to that same cell.
Click to expand...


--
Don Guillett
SalesAid Software
(e-mail address removed)
CarlosAntenna said:
Don,
Thanks for your reply.
I have tried modifying your code to suit, but I have not been successful.
My lookup table is on sheetname "LookUp", columns A:B and is a named range
"lookupt".
The sheet where I want to return the data is sheetname "TestLog" columns
Click to expand...
F
&
G, with different data going into each of the two columns beginning at
Click to expand...
row
4
thru 400.
Can you help me out with the translation?

My modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(active.cell, lookupt, 2, 0)
Application.EnableEvents = True
End Sub

I'm not sure what your second line is all about.

-- Carlos


Don Guillett said:
right click sheet tab>view code insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(Target, [lookuptable], 2, 0)
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
I want to do a lookup without using the lookup function. Here is the
idea:

I have a lookup table correlating Lot Numbers to Material Thickness.
In another sheet I want to enter a lot number into a cell and have it
return
the corresponding material thickness to that same cell.
I don't want to have a column on the sheet for lot numbers to lookup using
the VLOOKUP() function.

Since I can't do it with a function, I think it requires some VBA
programming. Very likely, this has come up before. Can someone
Click to expand...
point
me
to
a solution?

-- Carlos
Click to expand...
Click to expand...
Click to expand...
 
then something like this might work.

Private Sub Worksheet_Change(ByVal Target As Range)
if target.row<2 then exit sub
Application.EnableEvents = False
if target.column=5 then target.value = Application.VLookup(active.cell,
[lookupt], 2, 0)
if target.column=6 then target.value = Application.VLookup(active.cell,
[lookupt], 3, 0)
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
CarlosAntenna said:
<Attempt to clarify further>
Each row contains test data, two of the relevant pieces of data are plate
thicknesses of the top and bottom plate. But the plates are identified by
lot number rather than thickness. The lookup table correlates the lot
number with a thickness. So in each row of test data I want to type in the
lot number for the top plate in column F and for the bottom plate in column
G and have the corresponding thicknesses returned into the same cell where I
typed the lot number.

-- Carlos

Don Guillett said:
Your words taken at face value indicated that you wanted to type a value
into ONE cell and have the lookup returned to THAT cell. So, I set it up to
ONLY work on cell A3.
Click to expand...
columns
F
&
G, with different data going into each of the two columns beginning at
Click to expand...
row
4
thru 400.
Can you help me out with the translation?

My modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(active.cell, lookupt, 2, 0)
Application.EnableEvents = True
End Sub

I'm not sure what your second line is all about.

-- Carlos


right click sheet tab>view code insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Target.Value = Application.VLookup(Target, [lookuptable], 2, 0)
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
I want to do a lookup without using the lookup function. Here is the
idea:

I have a lookup table correlating Lot Numbers to Material Thickness.
In another sheet I want to enter a lot number into a cell and have it
return
the corresponding material thickness to that same cell.
I don't want to have a column on the sheet for lot numbers to lookup
using
the VLOOKUP() function.

Since I can't do it with a function, I think it requires some VBA
programming. Very likely, this has come up before. Can someone point
me
to
a solution?

-- Carlos
Click to expand...
Click to expand...
Click to expand...
 
Back
Top