| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 22237 | 2002-07-15 04:40:00 | Excel 2000 | Forrest (1056) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 62710 | 2002-07-15 04:40:00 | I am trying to work out a formula in Excel 2000 that will automatically select the customer number when a customer name is selected from a list . I have set up the form so that when a user reaches the Customer cell they are prompted to select a customer name from a drop down list (I generated the list by using the 'list' function in data validation) . I have tried using the IF function but am unsure of applying this formula to a list of 50 customer names and numbers (IF can cope only with 7 variables?) . Would VLOOKUP be more suitable? Any help would be most appreciated! B/rgds and many thanks in advance . Forrest |
Forrest (1056) | ||
| 62711 | 2002-07-15 04:48:00 | give your customer list array, plus the next column which contains the number, a name (i.e."custno") Use vlookup: =vlookup(entered_customer_name,custno,2,false) This should return the number adjacent (to the right of) the matching customer name (in column 2 relative to the name). |
godfather (25) | ||
| 62712 | 2002-07-15 05:00:00 | One way to achieve this: Create a two column list somewhere on your sheet, of Customer Names and customer Numbers. Add a Combo Box to the sheet from the Control toolbox Toolbar. Click the Design mode on the Control Toolbox Toolbar, rightclick the Combo Box and select Properties. Under 'Linked Cell' enter the cell reference where you want the result posted. Under 'ListFillRange' enter the 'array' range for your customer data (e.g G10:H20) Under 'BoundCloumn' enter 2 (column in array for Customer Number) Under ColumnCount enter 2 Under ColumnWidths enter ;0 (to hide second column in combo box) Click out of Design Mode and try :) |
wuppo (41) | ||
| 62713 | 2002-07-26 04:58:00 | Many many thanks for your replies both solutions worked a gem. :) | Forrest (1056) | ||
| 1 | |||||