cell mandotary

[max_max]

Hello to all.
I have an excel sheet in which you have to fill in rows in orbit.
Is this data validation possible?
Example for E6: if A6 is compiled E6 is mandotory
Example for E10: if A10 is compiled E10 is mandotory
Example for E100: if A100 is compiled E100 is mandotory
etc...
A greeting and thank you.
max_max

[CAntosh]

It's not perfect, but I think you can get close with data validation. If you have your headers in row one, select A2:E1000 and use the following custom Data Validation formula:

=COUNTIFS($A$1:$A1,"<>"&"",$E$1:$E1,"")<1

The Data Validation will prevent you from entering data in a new row if there is an entry in A that still needs data in the corresponding column E. You can also use conditional formatting to highlight the cells in E that require entries. If you're not planning to enter data from top to bottom, you can alter the COUNTIFS to better suit your method of data entry. Take a look at the attachment to see if it will work for you:

[max_max]

Hi cantosh,
Is not so accurate the data validation must work horizontally
max_max

[CAntosh]

Am I understanding correctly that additional entries in A, as well as columns B, C, and D must be blocked until a value has been entered in row E? Try the following:

Data validation formula for A2:A20: =COUNTIFS($E$2:$E2,"",$A$2:$A2,"<>"&"")<2
Data validation formula for B2:E20: =COUNTIFS($E$2:$E2,"",$A$2:$A2,"<>"&"")=0

The attachment only covers the first 20 rows, but you can extend the validation as far as you need to. Similarly, you can extend the range of the B2:E20 rule to include columns beyond column E and it should still work. Take a look at the attachment to see if it will work:

[max_max]

Hi cantosh,
only 1 rule:
if A is empty E is mandatory.
I hope I have explained.
max_max

[CAntosh]

Thank you for the clarification, I was under the impression that if A was NOT empty, E must be filled, which was a bit backwards.

Can you clarify how data is entered? What should be blocked until the user enters something in E? I don't think you want users being forced to fill in all one million rows in column E before they can do anything else, so what entries are you trying to block until data has been entered in E?

[max_max]

All cells are not blocked,
Columns B / C / D / F / G etc. can remain empty.
max_max

[CAntosh]

I'm afraid I don't understand what is supposed to happen. Can you please provide a more detailed explanation of how the sheet is used? What data is being entered? Where? In what order? When you say E is mandatory, what does that mean for your user?

[max_max]

Hope it is understandable

[CAntosh]

If I am a user, then I should:

1) Enter a value in E2
2) Enter a value in A2
3) Enter a value in E3
4) I should enter a value in A3, but if I forget and try to move on to E4, then data validation should prevent me from entering anything in E4 until A3 has been filled

or is it:

1) Enter a value in E2
2) Enter a value in A2
3) I try to enter a value in A3, but the data validation tells me I need to enter a value in E3 before I can add anything to A3

Is either of these two interpretations correct?

[max_max]

None of the 2.
Then:

I insert a value in A2 then a value in E2 = correct = no data validation
No value in A3 then a value in E3 = error = data validation
I enter a value in A4 then a value in E4 = correct = no validation
No value in A5 then a value in E5 = error = data validation

Sorry unfortunately must use google translator

[CAntosh]

I think I understand now. I'm using the following Data Validation formula for E2:E20:

=COUNTA($A2:$A2)=1

This rule should only allow the user to post in E if there is a post in the same row in A. Hopefully I am understanding your request correctly now. I wish I could speak Italian so we could communicate better! Please let me know if there are still problems:

[max_max]

Hi cantosch,
it is ok!
Thank you very much.

in Italian the data validation formula is:
=CONTA.VALORI($A2:$A2)=1
in Italian the conditional formatting is:
=E($A2<>"";$E2="")

max_max

[CAntosh]

haha, I'm glad I could help! Good luck!