Data Validation Error

[Dileep Chandran]

Hi,

I have given a data validation for A1. The column is validated to
accept the data only if B1 is true.

The issue is, when i drag down the validation from A1 till A100, the
validation is not changing respectively.

The validation in A2 should accept data only if B2 is true and not when
B1 is true.

It wont change like formulas, when we copy and paste the validation to
next cell?

Any help is greatly appreciated.

-Dileep

 

[Guest]

Hi Dileep,

After you set the validation on A1, select A1 to A100 and click
Data-Validation again. Excel will ask you if you want to extend the
validation on A1 to the other cells.

Cheers
Marjo

 

[Dileep Chandran]

Hello Marjo,

What I need is, the validation in A1 should change respectively when we
drag it till A100.

I have given the formula =IF(B1)=False in the data validation in A1

But when I copy the same validation to A2 the formula should change to
=IF(B2)=False.

Is it possible? Is my question clear?

Thanks for the help.

-Dileep

 

[Bob Phillips]

Dileep,

As Marjo says you can select A1:A100 and enter the data validation in one
hit, no need to drag copy.

Notwithstanding that, it works fine for me, the formula adjusts. I did just
use a formula of =B1, no need for the IF etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dileep Chandran]

Thats fine. But its not working if we are refering to another sheet.

Say if the forumla is =Indirect(Sheet2!B1)=False

Any idea?

-Dileep

 

[Bob Phillips]

ah, see if helps when you give us all the details.,

Use

=INDIRECT("Sheet2!B"&ROW(A1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dileep Chandran]

Bob,

Its not working. Whats that ROW(A1)?

-Dileep

 

[Bob Phillips]

It is just a way to get an incrementing number, 1,2,3, etc.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)