Help please with a lookup problem
 

I am trying to use a form to lookup data from another sheet and struggling

Sheet1 contains three columns "qty, desc, price"
My form sheet "DATA Sheet" has the same three columns.

I wish to type in the description column on the form any part of a product
name and then the function to either pull in the correct one or provide a
list of matching types but it also needs to pull in the price as well.

Where do I begin??

Help please with a lookup problem
 

How many product names do you have?

Why don't you just create a drop down list and then you can select the
specific product from the list. This would be much easier than what your
describing.

--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
I am trying to use a form to lookup data from another sheet and struggling

Sheet1 contains three columns "qty, desc, price"
My form sheet "DATA Sheet" has the same three columns.

I wish to type in the description column on the form any part of a product
name and then the function to either pull in the correct one or provide a
list of matching types but it also needs to pull in the price as well.

Where do I begin??

Help please with a lookup problem
 

Fantastic idea - setup a list and I can see the benifit. However leads me to
another problem - I have 100s of products and I may need to enter a part
description (ie any part of name) can it filter this down in some way

Products
Code Description Price
123 widget 10.00
321 gadget 20.00
456 spinning bit 30.00

Ok my list picks out the "description" on my Invoice sheet in the list
properties
=Description

Then on my invoice page in the price I have this
=VLOOKUP(C7,Products!$B$2:$C$6,2,FALSE)

It works great - but I may wish to just type in "get" and the list needs to
filter out
widget and gadget

??
many thanks for the lead anyway


"T. Valko" wrote:

How many product names do you have?

Why don't you just create a drop down list and then you can select the
specific product from the list. This would be much easier than what your
describing.

--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
I am trying to use a form to lookup data from another sheet and struggling

Sheet1 contains three columns "qty, desc, price"
My form sheet "DATA Sheet" has the same three columns.

I wish to type in the description column on the form any part of a product
name and then the function to either pull in the correct one or provide a
list of matching types but it also needs to pull in the price as well.

Where do I begin??

Help please with a lookup problem
 

I'm not sure how you'd do that: type "get" and filter out widget and gadget.
But, you could use a combo box from the Control Toolbox and get an
autocomplete functionality. You'd type in "W" and the list would show all
products that start with "W". You type in "Wi" then the list would show all
entries that start with "Wi".


--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
Fantastic idea - setup a list and I can see the benifit. However leads me
to
another problem - I have 100s of products and I may need to enter a part
description (ie any part of name) can it filter this down in some way

Products
Code Description Price
123 widget 10.00
321 gadget 20.00
456 spinning bit 30.00

Ok my list picks out the "description" on my Invoice sheet in the list
properties
=Description

Then on my invoice page in the price I have this
=VLOOKUP(C7,Products!$B$2:$C$6,2,FALSE)

It works great - but I may wish to just type in "get" and the list needs
to
filter out
widget and gadget

??
many thanks for the lead anyway


"T. Valko" wrote:

How many product names do you have?

Why don't you just create a drop down list and then you can select the
specific product from the list. This would be much easier than what your
describing.

--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
I am trying to use a form to lookup data from another sheet and
struggling

Sheet1 contains three columns "qty, desc, price"
My form sheet "DATA Sheet" has the same three columns.

I wish to type in the description column on the form any part of a
product
name and then the function to either pull in the correct one or provide
a
list of matching types but it also needs to pull in the price as well.

Where do I begin??

Help please with a lookup problem
 

Ok that sounds more like it will have to search further, I have done this a
thousand times in access just need to do it using excel - many thanks

"T. Valko" wrote:

I'm not sure how you'd do that: type "get" and filter out widget and gadget.
But, you could use a combo box from the Control Toolbox and get an
autocomplete functionality. You'd type in "W" and the list would show all
products that start with "W". You type in "Wi" then the list would show all
entries that start with "Wi".


--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
Fantastic idea - setup a list and I can see the benifit. However leads me
to
another problem - I have 100s of products and I may need to enter a part
description (ie any part of name) can it filter this down in some way

Products
Code Description Price
123 widget 10.00
321 gadget 20.00
456 spinning bit 30.00

Ok my list picks out the "description" on my Invoice sheet in the list
properties
=Description

Then on my invoice page in the price I have this
=VLOOKUP(C7,Products!$B$2:$C$6,2,FALSE)

It works great - but I may wish to just type in "get" and the list needs
to
filter out
widget and gadget

??
many thanks for the lead anyway


"T. Valko" wrote:

How many product names do you have?

Why don't you just create a drop down list and then you can select the
specific product from the list. This would be much easier than what your
describing.

--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
I am trying to use a form to lookup data from another sheet and
struggling

Sheet1 contains three columns "qty, desc, price"
My form sheet "DATA Sheet" has the same three columns.

I wish to type in the description column on the form any part of a
product
name and then the function to either pull in the correct one or provide
a
list of matching types but it also needs to pull in the price as well.

Where do I begin??

Help please with a lookup problem
 

See Debra Dalgleish's site for pointers on using the Combobox with DV as Biff
suggested.

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP


On Wed, 21 Nov 2007 14:24:02 -0800, CP wrote:

Ok that sounds more like it will have to search further, I have done this a
thousand times in access just need to do it using excel - many thanks

"T. Valko" wrote:

I'm not sure how you'd do that: type "get" and filter out widget and gadget.
But, you could use a combo box from the Control Toolbox and get an
autocomplete functionality. You'd type in "W" and the list would show all
products that start with "W". You type in "Wi" then the list would show all
entries that start with "Wi".


--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
Fantastic idea - setup a list and I can see the benifit. However leads me
to
another problem - I have 100s of products and I may need to enter a part
description (ie any part of name) can it filter this down in some way

Products
Code Description Price
123 widget 10.00
321 gadget 20.00
456 spinning bit 30.00

Ok my list picks out the "description" on my Invoice sheet in the list
properties
=Description

Then on my invoice page in the price I have this
=VLOOKUP(C7,Products!$B$2:$C$6,2,FALSE)

It works great - but I may wish to just type in "get" and the list needs
to
filter out
widget and gadget

??
many thanks for the lead anyway


"T. Valko" wrote:

How many product names do you have?

Why don't you just create a drop down list and then you can select the
specific product from the list. This would be much easier than what your
describing.

--
Biff
Microsoft Excel MVP


"CP" wrote in message
...
I am trying to use a form to lookup data from another sheet and
struggling

Sheet1 contains three columns "qty, desc, price"
My form sheet "DATA Sheet" has the same three columns.

I wish to type in the description column on the form any part of a
product
name and then the function to either pull in the correct one or provide
a
list of matching types but it also needs to pull in the price as well.

Where do I begin??