Validation and Lookup Functions

[smudge]

I'm using a couple of 'list-validations' to keep my worksheet les
cluttered. I wanted to do something a little smarter with thes
functions though. I'm trying to match the selected items from th
validation (pull down menu) with a lookup function for data in a tabl
with not only column headers but row headers too. This is what I need
but can't get working:

My first validation, when selected, needs to look ALONG the row wit
the selected heading, and return the value from under the secon
validation which looks DOWN a column. Thus:-

VALIDATION Menu1 Select (Thur) VALIDATION MENU2 Select (WED)

TABLE:

MON TUE WED THUR FRI SAT SUN (Column Headers)
TUE--------|
WED-------|
THUR--(VALUE)
FRI
SAT
SUN

(Row Headers)

I need a clever lookup because these two lookups only return one value
i.e. "VALUE"a result I want placed in another cell, under Validatio
Menu1.

To make matters worse, I would like to use a dependant list idea t
exclude from VALIDATION MENU2 whatever was selected in VALIDATIO
MENU1. In otherwords, in the above example, I selected "THUR" first
The second Validation menu should not contain "THUR". I see thi
getting complex, so wondered if these functions would be best sittin
in a worksheet module?

Any ideas are greatly accepted. I have read many great tips but can'
combine all of these ideas

 

[Frank Kabel]

Hi
see below

I'm using a couple of 'list-validations' to keep my worksheet less
cluttered. I wanted to do something a little smarter with these
functions though. I'm trying to match the selected items from the
validation (pull down menu) with a lookup function for data in a table
with not only column headers but row headers too. This is what I need,
but can't get working:

My first validation, when selected, needs to look ALONG the row with
the selected heading, and return the value from under the second
validation which looks DOWN a column. Thus:-

VALIDATION Menu1 Select (Thur) VALIDATION MENU2 Select (WED)

TABLE:

MON TUE WED THUR FRI SAT SUN (Column Headers)
TUE--------|
WED-------|
THUR--(VALUE)
FRI
SAT
SUN

Try the following (assumption: row 1 is your heading row)
=INDEX(A2:G7,MATCH(validation_cell_1,A2:A7,0),MATCH(validation_cell_2,A
1:G1,0))
I#m not so sure with your exact column/row headings (due to linebreaks
in my newsreader) but this formula should give you the general idea

To make matters worse, I would like to use a dependant list idea to
exclude from VALIDATION MENU2 whatever was selected in VALIDATION
MENU1. In otherwords, in the above example, I selected "THUR" first.
The second Validation menu should not contain "THUR". I see this
getting complex, so wondered if these functions would be best sitting
in a worksheet module?

For this kind of dependet lisboxes you may have a look at
http://www.contextures.com/xlDataVal02.html


Frank