Excel 2003, Validation across range

[network_engineer]

Hi all,

Could somebody guide me on this please?

How would I ensure that only one cell across a range could be filled? E.g.

across five horizontal cells, say A1:E1, I want to restrict that only one of those cells may be filled. I.e. if A1 is filled (with text or numbers), then B1, C1, D1, and E1, may not be filled.

Optionally, would be great if A1 is filled, all the others B1:E1 would be grayed out.

Kind regards!

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this:

Select A1:E1

Data|Validation|Settings tab|Allow: Custom|Formula: =COUNTA($A1:$E1)=1|OK

 

[Peter_SSs]

To add the shading as well ..

Select A1:E1 again

Conditional Formatting|New Rule|Use a formula ..|=AND(A1="",COUNTA($A1:$E1)>0)|Format...|Fill tab|choose grey|OK|OK*

* These instructions vary a little between versions. Post back with version if you need further help with this.

 

[network_engineer]

Hi Peter,

Thanks, I tried copying this, exactly as =COUNTA($A1:$E1)=1|OK

PS: Corrected! I typed =(COUNTA($C4:$H4)=1 and pressed the OK

However, I am getting the error "The formula you typed contains an error."

Kind regards,
Ben

 

[network_engineer]

Hi again Peter,

It worked, I just modified it a little: =(COUNTA($C4:$E4)=1).

Question: How would I replicate this across all the other rows, without having to set the validation for each row?

Thanks!

 

[markmzz]

Network_engineer,

Select the range C4:H4 and use Edit/Copy.
Then, for example, select the range C5:H100 and use Edit/Paste Special/ option Validation and click OK.

Markmzz

 

[network_engineer]

Thanks Markmzz, Peter!

Could one of you please explain me the shading part again? I did not understand that. What I am trying to do is, e.g. A1:E1, if any of them is filled in, e.g. c1 has been filled, then I also wanted to gray out the other cells, A1,B1,D1,and E1.

Kind regards!
Ben

 

[Peter_SSs]

Could one of you please explain me the shading part again? I did not understand that. What I am trying to do is, e.g. A1:E1, if any of them is filled in, e.g. c1 has been filled, then I also wanted to gray out the other cells, A1,B1,D1,and E1.

1. I'm confused about what columns you really are using. This says A:E as did an earlier post, but in between you seemed to be changing to just columns C:E as evidenced here:

Hi again Peter,

It worked, I just modified it a little: =(COUNTA($C4:$E4)=1).

2. This has redundant parentheses. Just as good would be:
=COUNTA($C4:$E4)=1

Question: How would I replicate this across all the other rows, without having to set the validation for each row?

3. Apart from markmzz's suggestion you could also select the whole range first, then apply the Data Validation as if you were just applying it for the first row. The other rows will have the DV adjusted appropriately.

 

[Peter_SSs]

Could one of you please explain me the shading part again? I did not understand that. What I am trying to do is, e.g. A1:E1, if any of them is filled in, e.g. c1 has been filled, then I also wanted to gray out the other cells, A1,B1,D1,and E1.

1. Select A1:E??

2. Format|Conditional Formatting...|Condition 1|Formula is| =AND(A1="",COUNTA($A1:$E1)>0)|Format...|Patterns tab|select gray|OK|OK

 

[network_engineer]

Hi Peter,

You are right, I changed the structure a bit! Sorry! the correct cells are C4 through E4, but now that part is done and works! Thanks!

Could one of you please explain me the shading part again? I did not understand that. What I am trying to do is, e.g. C4:E4, if any of them is filled in, e.g. c4 has been filled, then I also wanted to gray out the other cells, D4 and E4. Alternatively, if D4 has been filled, then I wish that C4 and E4 would be greyed out.

Thanks again.

Kind regards.
Ben