Excel: Survey validation per cell and THEN per row 1

[03Explorer]

I am building a dynamic survey system in MS Access that kicks out Excel sheets dynamically based on criteria defined in Access. My question isn't about Access but how I can lock the document from people tampering with it other than checking boxes. The layout is simple a question in a row and there are 6 columns for answers to be populated with either "x" or "X" or "NA" (without quotes). I have the populating option set with validation rules... however I need to make sure someone can't populate more than one of those 6 columns. Is there a trick other than using VBA? I don't have a vision of an outcome other than color WHOLE row RED if more than one populated per row. That is what I envision in my mind, but not sure if that is an easy setup and I really don't want to mess with VBA for it (if I can help it). Another thought is a logic that only allows one cell in a row to be populated at a time (denied trying to populate the second column in that ROW).

Anyone have insight into this arena??

Thanks!
Rob

 

[SkipVought]

Hi,

How about...

=COUNTA(YourRange)=1

..as the custom Data > Validation?

For instance, lets say that rows 1:3 columns A:F need the validation.

Select this area A1:F3

enter this Custom formula
=COUNTA($A1:$F1)=1

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

...however there’s no way other than via VBA to prevent entry in the next row before the previous row is entered.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[03Explorer]

Thank you Skip. I Guess I was looking to validate one row upon itself, not collectively. I can make a hidden column (K) that tells me that someone populated two columns in that row, making that row of data invalid. I want to know if I can restrict the user to entering ONLY one value per row... they can't move on until there is only one X or NA populated in that row.

I tweaked the survey so I can share.

I added column K for the time being... I am thinking of making this either hidden or white text. The only areas that are unprotected cells are c:J, the rest will be protected (simply by protecting the sheet).

Note: the survey will have anywhere from two sheets up to 11 sheets. Each will be an independent survey

Thanks in advance,
Rob

 

[SkipVought]

Select C3:I4

In Data > Validation > CUSTOM enter this formula
[tt]
=AND(COUNTA($C3:$I3)=1,OR(C3="x",C3="X",C3="NA"))
[/tt]

they can't move on until there is only one X or NA populated in that row.

Not without VBA. And here, you need to FORCE the user to ENABLE MACROS. and the only way I know how to do this is to have a Workbook.Open automatic macro that displays a splash sheet telling the user to ENABLE MACROS bofore they can even see the survey. Upon enabling macros the survey sheets are made visible and the Splash sheet is hidden.

When the user Closes the workbook, the Workbook.Close event SAVES the workbook in such a state that when it opens again the Splash sheet is the only sheet visible.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[03Explorer]

Thanks again Skip! I like the method given above, but I caught a glitch in your suggestion. Line1 should be Select C3:I3 (right?)

Great suggestion, I am not sure of the logic in your formula (I don't recognize it enough that I would have derived it on my own!) Great job!!

Rob

 

[SkipVought]

Select C3:I4 ALL SURVEY RESPONSE ROWS in the SURVEY RESPONSE COLUMNS

Just as one should do to FORMAT any range, you first select the entire range to be formatted.

In this case, ALL rows and columns to be formatted via Data > Validation should first be selected.

Then the Custom Data Validation format is entered with respect to the cell in the TOP LEFT of the SELECTION.

Hence,
[tt]
=AND(COUNTA($C3:$I3)=1,OR(C3="x",C3="X",C3="NA"))
[/tt]
...the logic of which is...
[An entry in the row range C:I can only contain 1 value] AND [the value must be “x” OR “X” OR “ NA”]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[03Explorer]

Oh, I was thinking possibly that's what you were getting to... whole range. However, When I was reading the formula it was specific for the one (first) row. Which led me to believe it was a per row action vs per whole range... which I presume the formula updates itself per row.

Very nice... I like it! Thank you again Skip

 

[SkipVought]

Yes, the formula is written with respect to the TOP LEFT cell in the SELECTION.

So COUNTA() is counting occurrnces of a value in the reference range $C3:$I3 in ANY cell in row 3 from C to I or in row 4 from C to I. That’s what the DOLLAR sign means: within the SELECTION range or range of application if you’re doing COPY n PASTE: Hold C and I constant or absolute across columns, but don’t hold the row reference constant or absolute.

But you also wanted only certain values to be allowed in ANY of the columns in the SELECTION range. So here we have C3=... in the formula, which means that in column F in row 4, if you select that cell and look at the Data > Valication > Custom Formula, you’ll see F4=...

In fact, the Custom Formula in F4 is
[tt]
=AND(COUNTA($C4:$I4)=1,OR(F4="x",F4="X",F4="NA"))
[/tt]
...because that's how the formula was written to function relative to the SELECTION.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}