So I have two columns of Data Validations:

Column A we'll call Cost Centers
Column B we'll call Accounts

If somebody puts in a Cost Center for a specific department which we
will call Advertising, then a different list shows up in Column B of
only Advertising Accts. I use the following formula in Column B for
my Data Validation:

=IF(ISERROR(VLOOKUP(A5,advertising,1,0)),accounts, advaccts) where
"accounts" is all accounts and "advaccts" is only advertising
accounts.

That works ok. I'm sure that is not the best way of doing it; but
here is my problem.

If I don't put anything in Column A, users can type anything into
Column B and there is no Data Validation. I was hoping that it would
use the "accounts" list as validation if nothing was entered into
Column A.

Thoughts on how to keep data validation on column B if nothing is put
into Column A?

I'm guessing you may just need to uncheck the "ignore blank" on the
validation dialogue. However, here's an alternative way to make dynamic
validation lists:
http://www.contextures.com/xlDataval02.html

--
Best Regards,

Luke M
"willwonka" wrote in message
...
So I have two columns of Data Validations:

Column A we'll call Cost Centers
Column B we'll call Accounts

If somebody puts in a Cost Center for a specific department which we
will call Advertising, then a different list shows up in Column B of
only Advertising Accts. I use the following formula in Column B for
my Data Validation:

=IF(ISERROR(VLOOKUP(A5,advertising,1,0)),accounts, advaccts) where
"accounts" is all accounts and "advaccts" is only advertising
accounts.

That works ok. I'm sure that is not the best way of doing it; but
here is my problem.

If I don't put anything in Column A, users can type anything into
Column B and there is no Data Validation. I was hoping that it would
use the "accounts" list as validation if nothing was entered into
Column A.

Thoughts on how to keep data validation on column B if nothing is put
into Column A?

Hi Luke,

Thanks, never ignore the obvious. I had unchecked it; but on Column A
which of course now makes no sense.

I knew of Deb's Dependent Validation page and have used it often; but
this was a little different in that based on ColumnA, it could a full
list of a subset of full list (Advertising) so I don't think that
would work with her example.

Thanks again.


On Mar 19, 12:07*pm, "Luke M" wrote:
I'm guessing you may just need to uncheck the "ignore blank" on the
validation dialogue. However, here's an alternative way to make dynamic
validation lists:http://www.contextures.com/xlDataval02.html

--
Best Regards,

Luke M"willwonka" wrote in message

...



So I have two columns of Data Validations:

Column A we'll call Cost Centers
Column B we'll *call Accounts

If somebody puts in a Cost Center for a specific department which we
will call Advertising, then a different list shows up in Column B of
only Advertising Accts. *I use the following formula in Column B for
my Data Validation:

=IF(ISERROR(VLOOKUP(A5,advertising,1,0)),accounts, advaccts) where
"accounts" is all accounts and "advaccts" is only advertising
accounts.

That works ok. *I'm sure that is not the best way of doing it; but
here is my problem.

If I don't put anything in Column A, users can type anything into
Column B and there is no Data Validation. *I was hoping that it would
use the "accounts" list as validation if nothing was entered into
Column A.

Thoughts on how to keep data validation on column B if nothing is put
into Column A?- Hide quoted text -

- Show quoted text -