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 tabview code insert thismodify to suitSAVE

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

"CarlosAntenna" wrote in message
...
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

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?


In article ,
"CarlosAntenna" wrote:

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

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" wrote in message
...
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?


In article ,
"CarlosAntenna" wrote:

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

Ah. That does require a Worksheet_Change() macro.

In article ,
"CarlosAntenna" wrote:

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.

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" wrote in message
...
right click sheet tabview code insert thismodify to suitSAVE

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

"CarlosAntenna" wrote in message
...
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

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

"CarlosAntenna" wrote in message
...
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" wrote in message
...
right click sheet tabview code insert thismodify to suitSAVE

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

"CarlosAntenna" wrote in message
...
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

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


"Don Guillett" wrote in message
...
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

"CarlosAntenna" wrote in message
...
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" wrote in message
...
right click sheet tabview code insert thismodify to suitSAVE

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

"CarlosAntenna" wrote in message
...
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

<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" wrote in message
...
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

"CarlosAntenna" wrote in message
...
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" wrote in message
...
right click sheet tabview code insert thismodify to suitSAVE

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

"CarlosAntenna" wrote in message
...
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

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

"CarlosAntenna" wrote in message
...
<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" wrote in message
...
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

"CarlosAntenna" wrote in message
...
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" wrote in message
...
right click sheet tabview code insert thismodify to suitSAVE

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

"CarlosAntenna" wrote in message
...
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