If statements with validation lists

Thanks again
Tony

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 again
Tony

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 again
Tony

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 again
Tony

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 again
Tony

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 again
Tony

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>