Lookup

[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

 

[Don Guillett]

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

 

[JE McGimpsey]

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?

 

[CarlosAntenna]

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

 

[JE McGimpsey]

Ah. That does require a Worksheet_Change() macro.

 

[CarlosAntenna]

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

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

 

[Don Guillett]

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.

--
Don Guillett
SalesAid Software
(e-mail address removed)

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

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

 

[CarlosAntenna]

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

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.

--
Don Guillett
SalesAid Software
(e-mail address removed)

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

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

 

[CarlosAntenna]

<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

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.

--
Don Guillett
SalesAid Software
(e-mail address removed)

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

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

 

[Don Guillett]

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)

<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

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.

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


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