Excel - Validation Nightmare

  • Thread starter Thread starter ajw150
  • Start date Start date
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
 
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)),"")
 
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
 
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.
 
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
 
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
 
Back
Top