Is Conditional validation possible in Excel?

[dnanak]

I have two columns A and B in the worksheet.

Cells in the column A lists some items (using the 'Data validation'). Now, there is a separate list of items in the column B.

What I want is to use 'B' as the source and conditionally list items in another column C. As an example, consider the following....

Cells in column A can take any of 1,2,3,4,5 (List created using data validation)

Column B consists of 11,12,21,22,31,32,41,42,51,52

Cells in C should list
11,12 if A is 1
21,22 if A is 2
31,32 if A is 3
41,42 if A is 4
51,52 if A is 5

Is this possible in Excel 2000? Or is there some programmatic way of doing it? Please explain....

Thanks,
Anand Krishnan
Quality Assurance Personell

 

[SkipVought]

Hi,
Do you mean that for Column C, If A=1 and If B=11 or B=12 Then C would equal the Value in B etc? Skip,
metzgsk@voughtaircraft.com

 

[jonsi]

If I'm reading your query correctly the answer lies in a nested IF statement similar to the one below:

=IF(A1=1,(CONCATENATE(B1,",",B2)),(IF(A1=2,(CONCATENATE(B3,",",B4))))) keep nesting for criteria 3,4,5

This way you would suit Criteria 1 always to the fixed options in column B Rows 1 & 2, Criteria 2 to Column B rows 3 & 4 allowing for text in between the two fixed options

...of course I may have read your Q totally wrong
GJ

 

[Guest_imported]

I did'nt mean both! May be I confused unnecessarily.

The keypoint which I am talking about is listing items in a drop down box for a cell. It is easy to do that by selecting 'Data validation' menu item and opting for the 'List'. Now, What i want is to list (in a drop down box) some values in the column C based on the value in column A. If A takes 1, C should list 11 and 12; If A takes 2, C should list 21 and 22; and so on. I hope you understood my requirement.

Bye,
Anand Krishnan

 

[jonsi]

Hope I'm understanding you correctly this time

take the following:
A1 is a drop down referencing F1:F5
B is the range 11,12,21,22 etc
C1 is a drop down referencing H1:H2
D & E are empty just for fun
F1:F5 are cells containing 1,2,3,4,5
H1 contains the following:
=IF($A$1=1,G1,(IF($A$1=2,G3,(IF($A$1=3,G5,(IF($A$1=4,G7,(IF($A$1=5,G9,"&quot))))))))
H2 has a copy of H1

result
You get a limited drop down in C1 based on the result of the formula relative to the contents of A1.

GJ

 

[Guest_imported]

That was just great! Thanks a lot...

- Anand krishnan