Excel - Validation Nightmare

A

ajw150

I am trying to get my head around the data validation – but struggling
Please can someone help:

Please see the attachment for more details.

Basically, Column A is one list, B another. So if a preference is mad
from both, the corresponding answer from Column C appears. But how ca
this be done? I am 99% sure data validation is the answer, but canno
get the paths to add up.

(Hope you follow)


I have studied the contexture site in some detail but still stuck!


Thanks

Andre

Attachment filename: eg1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51113
 
F

Frank Kabel

Hi
if your data validation listboxes are on a separate sheet in cell A1
and B1 try the following:
1. Create the data validation listboxes:
- on sheet 1 select the data range for column A, goto 'Insert - Name -
Define' and define a name for this range (e.g. 'Consequence')
- repeat the same for column B (e.g. name: 'Benchmark')
- goto cell A1 on the second sheet. goto 'Data - Validation'. Coose
'List' and enter the formula
=Consequence
- repeat this for cell B1
- now you should have drop down fields in A1 and B1
- you may delete the blank rows on the first sheet

2. Let C1 return the Risk Gap value. enter the following formula as
array formula (with CTRL+SHIFT+ENTER) in C1:
=IF(AND(A1<>"",B1<>""),INDEX(Sheet1!C1:C73,MATCH(1,(Sheet1!A1:A73=A1)*(
Sheet1!B1:B73=B1),0)),"")
 
A

ajw150

Thanks Frank, thats excellent. The only snag is, that in Column A - m
fault - there are many multiple entries - which makes the lis
complicated.

The reason I had put that in is because the user has to make tha
option with column B to obtain the result. Is there any way of jus
putting the consequence headings inA - and still matching them up.

Serious Probable
Serious Possible
Serious Remote
Significant Probable
Significant Possible
Significant Remote
Minor Probable
Minor Possible
Minor Remote


Thanks

Andre
 
F

Frank Kabel

Hi
no problem. just create two lists with only the relevant/unique entries
for A1 and B1. then use the selected entries on your current sheet as
lookup criteria.
 
A

ajw150

Thanks. I thought I understood the basics of lookup but not too sure ho
I would fit it into this example, because of matching up the data
 
A

ajw150

Sorry to pester - I am still stuck on this one. My column A contain
many similar entries which have to match with B to equal C. When th
user picks A, the system must know that only certain cells match t
give the correct result. If lookup is the answer can anyone help m
with the code?

Please.

Thanks

Andre
 
Top