Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Box with lookup facility
Hi all,
Don't know if this will test you or not. I'm trying to combine a drop down list along with a lookup facility. I have a list of job grades in say A2:A5 along with pay scales along the columns - Min/Med/Max. Whatever is input in a certain cell somewhere else on the same sheet for example further down in column A 'Manager Grade 1', I would like the lookup to default to the Max value in the corresponding cell in column B but leave the cell unprotected so the user can enter the correct value. To aid the user further I would like a dropdown box to show the correct values. I've tried using the standard dropdown and data validation but so far to no avail. Help anyone? Thank you, Colin. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
List Box with lookup facility
G'day Colin
You can quite easily combined a dropdown list with and table array The array can be on the same sheet or different if you like, the array in this example is set @ A1 on an assumed sheet name "Lookups" The example below assumes the following: 2 dropdowns, A1 = "Title" & B1 = "Grade" The result: C1 = =IF(A1="","",OFFSET(LookUp!$A$1,MATCH(A1,LookUp!$A $2:$A$20,0),MATCH(B1,LookUp!$B$1:$G$1,0))) The array would look like this: A B C D 1 Title/Grade 1 2 3 etc 2 Manager 100K 200K 300k etc 3 Slave 10K 11K 12K etc The main benefit of using an array is that you only have to update the values in the array when seasonal salary adjustments occur and not the values of individual cells in your main sheet. You could even expand the dropdown "Grade" list and array as you like. eg 1L = "Grade 1 Low" 1M = "Grade 1 Med".....etc Your array will obviously change to suit also. A B C D 1 Title/Grade 1L 1M 1H etc 2 Manager 100K 200K 300k etc 3 Slave 10K 11K 12K etc HTH Regards Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a 'floating key/ legend' facility? | Excel Discussion (Misc queries) | |||
Spelling Facility | New Users to Excel | |||
how do i use LOTUS combine facility in Excel | Excel Discussion (Misc queries) | |||
Excel should provide the facility to compare two spreadsheets | Excel Discussion (Misc queries) | |||
How do I turn off the text to speech facility in Exccel? | Excel Discussion (Misc queries) |