Allow Digital Sign Conditional with airSlate SignNow

Add subscriber conditional

hello and welcome to the video but Trump Excel I am so mad Bunsen and in this video I will show you how to create conditional data entry rules in Excel my conditional data entry I mean that when you enter a data in Excel it would only allow you to enter that if a pre-specified condition has been met and if the condition has not been met then it will show you an error so let's see a couple of examples here I have a list of countries and in cell c1 I want the user to enter one of these countries names I do not want the user to enter anything else except these names so to do that a very simple way would be to create a drop-down list here and I've already created one if you click on this arrow pointing downwards you would see that this drop-down list appears and here I have these names I can select a name and then it would appear in this cell but if I try and I enter something else let's say I try and enter X Y Z here and I hit enter atleast show me this error it'll say the value you entered is not valid and a user has restricted values that can be entered into this cell and I can retry or I can cancel in this case you can manually enter elements from this list so let's say I want to enter I can either go and select u.s. from this list or I can come here and manually enter this and it will be allowed because this is one of the entries which is allowed in this cell so to create this drop-down list simply go to the data tab here within data tools group we have data validation if I click on it it opens the data validation dialog box within the Settings tab in allow you have to select list and within list here you have to specify the source I have the source set as a 1 to a 6 you can extend the source to wherever your data resides and now when you click OK it show you this drop-down list now let's go back to the data validation dialog box and here you'd see these two options ignore blank and in-cell drop-down let me first talk about in-cell drop-down if I uncheck this and I come here you can see that the icon of the arrow pointing downwards is gone now it will not show me that drop-down list I can still enter these elements here so let's say I enter Canada then it will allow this else if I enter something else let's say I enter Brazil it will not allow me to enter the data but the drop-down thing is gone if you want it back make sure that you have this option checked and in case of ignore blank as of now it's checked let's see what happens I come here I delete what's there in the cell and I would try and hit enter which means that I'm leaving this cell empty and now when I hit enter it allows me to do that because here I have selected ignore blanks so I would select the cell go to data validation and here you can see I've selected ignore blanks I've checked this option but if I uncheck this and now again I come back here and I try and enter a blank leave this cell blank and I go ahead by pressing enter it will show me this error so these are two options that you can use drop down is something that I always use it looks cool and it gives the user to go through the list and then select the right element now in this case we have restricted the user to these elements within this list but let's say you want to leave it open-ended you want the user to enter anything it could be his name or it could be his or her date of birth or mobile number then these things are something that you cannot specify in a drop-down list so let's take an example from that I have this tab here form and here I've created a small form where I have the first name middle name and last name the date and mobile number now this could be a situation where you are asking the user to fill a questionnaire or a survey or a simple form like this and what I want to do is I don't want the user to enter the date before he has entered the first name and last name so I want him to go in a sequence first he should enter the name and then he should move to date but if he tries to enter date before entering the name then it should show an error so in this case let's say if I type a date here and I hit enter it would allow me to do that but I don't want this I want the user to first enter these two cells and then come to this so to do that we would have to again go back to data validation I would go to data data validation and here in the Settings tab I would select custom and what custom does is you can specify a formula and if that formula returns true then it allows the data entry but if it returns false then it does not allow data entry here so what I would do is I would keep this unchecked and I would use a formula and I would say and if this cell b1 let's press f4 to lock this is not blank comma cell b3 is not blank now what it would do is while I enter anything here it would first check whether this is true or not which means whether there is data in b1 and b3 or not if there is no data it will not allow the user to enter the date so let's see how it works I press ok now I would come here and I would try and enter a date and you can see it shows me an error it says the value you entered is not valid and let's see what happens when I try and enter my name here so I would enter my first name and my last name and now when I come here and I try and enter the date it would allow me to do this because the condition is met the condition the and condition that we use would now return true and this is allowed now you can also specify a date condition here so let's again go back to data validation dialog box and here you can also select a date condition and you can specify whether the start date and you can specify the start date and end date which means that if a user enters anything which is either before the start date or after the end date then it will not be entered here if you show an error else it will allow that so let's say I manually enter the start date as 1st October 2015 and here I make it as 10 October 2015 and I would click OK now let me delete these now it's not dependent on these cells it's only the date range and if I try and I enter say 1st January 2015 it will show me an error but if I enter let's say 3rd October then this is within the range I hit enter and this is allowed so this is another way of using data validation to restrict entry based on two date ranges you can also combine multiple conditions so let's say in this case you also want to restrict this based on whether these cells are filled or not so you want the date range to be 1st October to 10th October and these cells should be filled so to do that what we would do is let me create the condition here first and I would create an end condition and the first thing would be I need to check whether this cell is filled or not I would make is not equal to blank then the second condition is this cell is not equal to blank then the third condition is that that date range here should be first October 2015 so we would say if this cell f4 is greater than or equal to and then we would use the date formula and I would say the year is 2015 the month is 10 and the day is 1 and finally the fourth condition would be this cell F 4 is less than equal to again the date condition here which would be here is 2015 month is October and the date is 10th what I am doing here is I am checking for four conditions whether this is empty or not whether this is empty or not and whether the date in this is between these two dates which is 1st October to 10th October and if all these conditions are met then allow the data entry else show me an error now I would use this formula let me copy this formula and I would come here let me delete this I would go to data data validation and here I would select custom and within custom I would put this font lock and now when I click OK and come here let's try this so I would just fill my first name not not the last name and let's try and enter date which is within the range so it should be 3rd October 2015 and it will show me an error because I have not felt the last names let's do that I enter the last name here now I come here and I try and enter the date it would allow me to enter this because the condition here becomes true but if I try and enter something which is out of the range let's say I'm trying for 15th October 2015 it need not allow me to enter this because this condition is false so I would have to go back to this value so this is how you can combine multiple conditions and then you can create a data validation rule that would check for all those conditions and if those conditions are true then it would allow the data entry else it would not allow the data entry one minor thing that I want to highlight here is that in case when you're using dates since dates are stored as numbers you can also use numbers in data validation let's say for example this date here let's see what's the number for this date so I would go to format number and the number here is four two zero zero five so instead of using say date here I can also use a number which is which represents that date and you can also use another function such as we have already used date you can also use today if you want to restrict the data entry from the current date so these are the things that you can't so that's it in this video I hope you found this useful thank you and have a nice day