List Box with lookup facility

[Col]

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.

 

[NoodNutt]

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.