sims - forcing to fill a column based on the specific value entered in another column

[antonponniah]

Hi

 

Is it possible to force a user to fill in a column based on a specific value entered in another column, for example I have two columns in a mark sheet,

allowed values on Column1 are 1, 2 and 3

when a teacher enters 1 or 2, column 2 can be left blank

when a teacher enters 3 then teacher has to enter some value in the second column

important thing here is, this need to be automatically triggered.

 

Thanks in advance

[Esteban_Child_of_the_Sun]

The best you can do is add validation and highlight the cell with missing data in a color (typically red). Using a nested if then else it would be something like this:-

If

=> "0" Then "Data Present" else If = "3" then "Missing"(colored red) Also color

[antonponniah]

Thanks Esteban for the good idea. Only issue is a teacher can still ignore this and save the mark sheet without filling the column2 but I don't think we have any other choice like we do in a web application.

[Esteban_Child_of_the_Sun]

Another thing you could do is store the "Data Present" and "Missing" results in an aspect and run a report near the end of the data collection process to tell you who has ignored the large red box.

[antonponniah]

Great idea, certainly useful, Thank you Esteban!!

[antonponniah]

I have tested this and all working fine but needed to tweak a bit to get a better result as I also need to show the user that column2 should be left blank when column1 is not ‘3’, which means I have the following scenarios.

Column1 is ‘3’ and column2 is empty – Error, invalid data input

Column1 is 3 and column2 is NOT empty – All good

Column1 is NOT ‘3’ and column2 is NOT empty – Error, invalid data input

Column1 is NOT ‘3’ and column2 is empty – All good

I have created 2 additional columns which checks above two columns with ‘if then else’:

Column3 àif column1 is ‘3’ then assign 1 else 0

Column4 àif column2 is Not empty then assign 1 else 0

Another column (column5) which adds the above integers, which gives me 2,1and 0

Final column uses the ‘nested if then else’ which checks the column5 value to notify the user – 2 and 0 are good; invalid data input when the value is 1

Any feedback is welcome.