how to write a dependent validation with all numbers

  • Thread starter Thread starter mark.campsey
  • Start date Start date
M

mark.campsey

need to place a value in A1 and have an associated drop down menu auto placed in A2?
 
need to place a value in A1 and have an associated drop down menu auto
placed in A2?
Click to expand...

Here's one way.

Put the values for the drop-down lists in consectuive columns someplace; for example,
H1:H7 for A1=1
I1:I10 for A1=2
etc.

Then use
Data > Data tools > Data Validation > Data Validation
and in the dialog box choose
Allow: List
Source: =OFFSET(G1,0,A1,COUNTA(OFFSET(G1,0,A1,10000,1),1))
and fill in the remaining DV settings as desired.

If the A1 values are other constants (that is, if they aren't the counting numbers 1,2,3,...) first use MATCH(A1,...,0) with a list of the allowed A1 values to get a counting number for the Source (in place of simply A1).

Hope this helps getting started.

(I have Excel 2010.)
 
Back
Top