in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony

Tony,

You need to make at least 2 named ranges: List0 and List1

List0 should be a single cell, with the value n/a

List1 should be as many cells as you want for your dropdown list: enter the allowed values in that
range.

You can also have List2 and List3 if the numbers in column A are use in determining which list to
use.

Select cell B2, the choose Data / Validation. Select the Settings Tab, and select List in the
"Allow" box, and in the Source box, enter the formula

=IF(A2=0,List0,List1)

and make sure that you check the "in-cell dropdown" box.

If the numbers matter, then use a formula like in the Source box:

=IF(A2=0,List0,IF(A1=1,List1, IF(A1=2,List2,List3)))

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony

Tony,

Of course, I read your problem backwards <vbg

Use this formula for data validation on cell B2:

=IF(A2=0,List1,List0)

and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert"
on the error tab.

Then in Cell B2, enter the formula

=IF(A2<0,NA(),"")

and copy cell A2 down as far as you need.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony

Thanks can see what you mean but don't understand how you can have 2 formula
in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read
your notes wrong but would appreciate your clarification.
Thanks Tony

"Bernie Deitrick" wrote:

Tony,

Of course, I read your problem backwards <vbg

Use this formula for data validation on cell B2:

=IF(A2=0,List1,List0)

and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error alert"
on the error tab.

Then in Cell B2, enter the formula

=IF(A2<0,NA(),"")

and copy cell A2 down as far as you need.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony

=IF(A2<0,NA(),"")

goes into the cell, while

=IF(A2=0,List1,List0)

is used as the data validation source.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
Thanks can see what you mean but don't understand how you can have 2 formula
in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read
your notes wrong but would appreciate your clarification.
Thanks Tony

"Bernie Deitrick" wrote:

Tony,

Of course, I read your problem backwards <vbg

Use this formula for data validation on cell B2:

=IF(A2=0,List1,List0)

and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error
alert"
on the error tab.

Then in Cell B2, enter the formula

=IF(A2<0,NA(),"")

and copy cell A2 down as far as you need.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony

brilliant, thanks, just so i understand could you explain what the first
formula means after a2<2,
Thanks again
Tony

"Bernie Deitrick" wrote:

=IF(A2<0,NA(),"")

goes into the cell, while

=IF(A2=0,List1,List0)

is used as the data validation source.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
Thanks can see what you mean but don't understand how you can have 2 formula
in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read
your notes wrong but would appreciate your clarification.
Thanks Tony

"Bernie Deitrick" wrote:

Tony,

Of course, I read your problem backwards <vbg

Use this formula for data validation on cell B2:

=IF(A2=0,List1,List0)

and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error
alert"
on the error tab.

Then in Cell B2, enter the formula

=IF(A2<0,NA(),"")

and copy cell A2 down as far as you need.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony

Tony,

The first formula is simply meant to help you fill in the cells in column B with N/A's when the
corresponding values in column A are not 0. So, if A2 is 1, 2 or 3, B2 should have NA. Otherwise,
it is left blank to appear blank, and will have a pull down list that uses List1.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
brilliant, thanks, just so i understand could you explain what the first
formula means after a2<2,
Thanks again
Tony

"Bernie Deitrick" wrote:

=IF(A2<0,NA(),"")

goes into the cell, while

=IF(A2=0,List1,List0)

is used as the data validation source.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
Thanks can see what you mean but don't understand how you can have 2 formula
in b2, ie =IF(A2=0,List1,List0) and then =IF(A2<0,NA(),""). I may have read
your notes wrong but would appreciate your clarification.
Thanks Tony

"Bernie Deitrick" wrote:

Tony,

Of course, I read your problem backwards <vbg

Use this formula for data validation on cell B2:

=IF(A2=0,List1,List0)

and ignore the gibberish about List2 and List3. Also, make sure that you uncheck "Show error
alert"
on the error tab.

Then in Cell B2, enter the formula

=IF(A2<0,NA(),"")

and copy cell A2 down as far as you need.

HTH,
Bernie
MS Excel MVP


"Tony Houston" wrote in message
...
in two columns ay A & B.
In A2 there will be a number from 0-3. I want to show in B2 a dropdown list
but only if the value in A2 =0, all others should show n/a.

How can i make this work?
thanks Tony