Help with table lists or whatever you call it.

[Asiageek]

I need some help to create a table that when I enter some words of a product
item in my list, it drops down a box and selects the matching items initial
letters highlighted and selects the correct sales value after I choose the
product. I don't know what you call this but I need some help to start off
please.

When I type in ADSL for eg, a list pops up with all matching ADSL matches in
the Products Column and in the Sales Column the actual price when selected
using the keyboard mostly. I will need to add some new products also with
their sale values and be able to copy the products text for copy/paste
elsewhere. Everything else I can manage with easily like sum total, printing
areas... just setting this thing up.

Help much appreciated. Thank you.

The data is this:

Titles of columns -
Ph # | Ref # | Products | P/Code | Manual Data (MD) | Sales $ | Comments.

Products data -
ADSL 1 ($100)
ADSL 2 ($150)
ADSL 3 ($200)
DUN... ($50)

Sales $ data -
$100
$150
$200
$50

eg.
DUN = $50
ADSL 2 = $150

Will look like this:
Column: Ph # | Ref # | Products | P/Code | Manual Data (MD) | Sales $ |
Comments.
Data: 01234.. | 1234567 | DUN...($50) | 1234 | blah blah | $50 | blah blah

 

[JLatham]

I think what you need are two things:
Data Validation to get the list to choose the Product (ADSL) entry from.
Then you need some VLOOKUP() formulas across the row to get information from
a data table you'd set up elsewhere - the Products data list you've shown in
your example.

For your convenience later, I'd set my data table up somewhere like this, on
a sheet of its own
A B
1 ADSL 1 100
2 ADSL 2 150
3 ADSL 3 200
4 Dun... 50
5 ....more entries on down many rows (1024 maximum)
20 LastEntry 0

At the bottom of the current list, put in a 'marker' entry like I've shown
as LastEntry. Now when you add to that list, choose that row and Insert a
row above it to keep things working on the other sheets. If you just added
to the bottom of the list, new entries will most likely not be seen in your
list elsewhere.

While you are still on that sheet, give some Names to the areas with
information in them. In the example, choose A1:A20 and then up in the Name
Box (where it usually shows the address of the cell you've got selected) type
in a name to use to reference this range such as "ADSLList" or "ProductList"
(no double-quote marks, just the words) and hit the [Enter] key.

Then highlight/choose all of the cells in the table from A1:B20 and do the
same thing: give it a name like ADSLTable (don't forget to press that [Enter]
key or the name won't 'stick').

OK, back on your working sheet, choose the cell(s) where you want the user
to be able to pick from the list. Choose Data | Validation and then choose
'List' as the type of data and down where it asks for the source of the list
enter
=ADSLList
[OK] and you're done there.

In the column where you want to look up the actual price when someone
selects from the list you need a VLOOKUP formula. Assume that your list is
in column A of any row, and that the actual price is going to show up over in
column D (doesn't really matter, where this is, formula is same, but I want
to keep things very clear).
In column D of that row (we'll pretend we are on row 12) enter
=IF(ISNA(VLOOKUP(A12,ADSLTable,2,0)),"",VLOOKUP(A12,ADSLTable,2,0))

Now, you mentioned "...selects the matching items initial letters..." and
I'm thinking you're wanting a kind of auto-complete feature so that if your
list was alphabetized, when you type a B it jumps on down into the list to
the beginning of the B entries. Data Validation doesn't do that - you have
to scroll down to find things.

Now, Debra Dalgliesh has some help for that by using a combo box in
conjunction with the DataValidation to give you that ability. Her solution
over at Contextures.com is perfect for this:
http://www.contextures.com/xlDataVal11.html