Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with table lists or whatever you call it.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with table lists or whatever you call it.
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(A1 2,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 "Asiageek" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table lists | Excel Discussion (Misc queries) | |||
Multiple tables with lists to one table. | Excel Discussion (Misc queries) | |||
ADO call to lookup table | Excel Discussion (Misc queries) | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
Excel ? Do Not Call Table | Excel Worksheet Functions |