• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Handling required fields on Spread Sheet

I have three cells that are needed to come up with a calculation in a fourth field. The calculation field is not always needed by the user. There are times when the user needs this Calculation.

Challenge, when the three non required fields are needed to be used in order to come up with a calculation. How do I make sure all the other three fields become a needed input field? Also, How do I ensure that information is a non-zero number?

How do I force the user to enter non-zero numbers into all of the three fields to come up with the calculation when the calculation is needed by the user.
 
Last edited:
Hi Clarence ,

How will the user declare their intention to use all 3 fields for calculation ? Can we have a checkbox , which if checked will mean that the 3 fields are needed , and which if unchecked will means that the 3 fields are not needed ?

If this is acceptable , then the checkbox cell link can be used in data validation to ensure that the 3 fields are made mandatory as and when the checkbox is checked.

Narayan
 
Hi Clarence ,

How will the user declare their intention to use all 3 fields for calculation ? Can we have a checkbox , which if checked will mean that the 3 fields are needed , and which if unchecked will means that the 3 fields are not needed ?

If this is acceptable , then the checkbox cell link can be used in data validation to ensure that the 3 fields are made mandatory as and when the checkbox is checked.

Narayan
There are twenty input fields on the spread sheet. There is one field that is not always required for coming up with a total. This total is created when the twenty fields of information are Total. The one field that is not required will sometime be required and there is a formula that needs two input values that return the appropriate value for the non required field when it becomes required. These two values are not constant and will change. To keep the user from having to use hand held calculator to come up with the value for the one field that sometime need to be include to come up with the final total. I added the two fields needed for the function onto the form and this give me a total of twenty two field when I add these field to form as non required field. I can not think of a better way to have the user input the other two value when need.
 
Hi Clarence ,

How will the user declare their intention to use all 3 fields for calculation ? Can we have a checkbox , which if checked will mean that the 3 fields are needed , and which if unchecked will means that the 3 fields are not needed ?

If this is acceptable , then the checkbox cell link can be used in data validation to ensure that the 3 fields are made mandatory as and when the checkbox is checked.

Narayan
I used the below example for handling require and non-required field. But, I am also looking at a simply creating a vba dialog input forms. I am not sure which will be easier for me to manage these input. I am not sure popping up form are the best way to go. I thinking of how a user inputting this information. I may have more field added with this same scenario.

http://www.contextures.com/exceldataentryupdateform.html
 
Hi Clarence ,

How will the user declare their intention to use all 3 fields for calculation ? Can we have a checkbox , which if checked will mean that the 3 fields are needed , and which if unchecked will means that the 3 fields are not needed ?

If this is acceptable , then the checkbox cell link can be used in data validation to ensure that the 3 fields are made mandatory as and when the checkbox is checked.

Narayan
Back to your question of declaring the intention of when to use the 3 fields for the one calculation field. There maybe 100 records entered and only 20 records may need those 3 fields with different values which are not constant. Again, I looked at the contextures example. I am not sure this is appropriate approach. I am not sure if this will be view as out of date (legacy) approach to solving this problem. I want to come up with something that will be very good user experience when come to inputting this information. I do not want it to be to easy for the user input bad data. I am currently working with excel 2007.
 
Back to your question of declaring the intention of when to use the 3 fields for the one calculation field. There maybe 100 records entered and only 20 records may need those 3 fields with different values which are not constant. Again, I looked at the contextures example. I am not sure this is appropriate approach. I am not sure if this will be view as out of date (legacy) approach to solving this problem. I want to come up with something that will be very good user experience when come to inputting this information. I do not want it to be to easy for the user input bad data. I am currently working with excel 2007.
Hi Clarence ,

If you can upload a sample workbook it might be easier to suggest something appropriate.

Narayan

Here is an example of the input layout. The fields in gray or optional. If any field have a value, all field must have a value. These value are not constant but can change each for each new input records.
 

Attachments

  • Book1.xlsx
    12.1 KB · Views: 9
Hi Clarence ,

Please confirm / correct my understanding :

The fields in cells D4 through D9 , and cells D11 through D14 are the optional fields.

Either all the above cells should be left blank , or all of them should be populated ; is that correct ?

Implementing such a rule through Data Validation is difficult , since when D4 is populated , there is no way for Excel to determine whether the user is going to populate the remaining cells D5 through D9 and D11 through D14.

VBA can help ; is it acceptable ?

Narayan
 
Hi Clarence ,

Please confirm / correct my understanding :

The fields in cells D4 through D9 , and cells D11 through D14 are the optional fields.

Either all the above cells should be left blank , or all of them should be populated ; is that correct ?

Implementing such a rule through Data Validation is difficult , since when D4 is populated , there is no way for Excel to determine whether the user is going to populate the remaining cells D5 through D9 and D11 through D14.

VBA can help ; is it acceptable ?

Narayan

You are correct with the explanation that fields in cells D4 through D9 , and cells D11 through D14 are the optional fields. Either all the above cells should be left blank , or all of them should be populated. VBA code is acceptable for solving this problem.
 
Hi Clarence ,

The user may find this somewhat irritating , but see if it fulfills your requirement.

Narayan
 

Attachments

  • Book1.xlsm
    21.1 KB · Views: 6
Hi Clarence ,

The user may find this somewhat irritating , but see if it fulfills your requirement.

Narayan
This is a very power technique of using Union and Intersection. Can the union of these section(cells D4 through D9 and D11 through D14 ) be decouple. These two sections or independent of each other. The reason that I ask this is because I may have more 2 or 3 section like this. I know you said this maybe irritating to the user but it seem like a very good solution.
 

Attachments

  • Book_Union_Intersection.xlsm
    20.4 KB · Views: 4
Hi Clarence ,

See if this OK. In case you are going to have more such areas , we can change the code so that it uses arrays to simplify the code.

Narayan
 

Attachments

  • Book_Union_Intersection.xlsm
    21.4 KB · Views: 6
Hi Clarence ,

See if this OK. In case you are going to have more such areas , we can change the code so that it uses arrays to simplify the code.

Narayan
Using arrays may make this more simpler. Let me review this to understand how this work. General question would you personal have the spread sheet layout this way? Is using dialog screen a better choice? I am very concern with the UI/UX. Are you limited in what you can do with the way cells should be arranged for quick input or better user experience.
 
Using arrays may make this more simpler. Let me review this to understand how this work. General question would you personal have the spread sheet layout this way? Is using dialog screen a better choice? I am very concern with the UI/UX. Are you limited in what you can do with the way cells should be arranged for quick input or better user experience.
The message that pop up give the actual range. When I hide the cell reference the user will not know which field is D4 or D5 etc... How do I grab which cell is missing the value.
 
Hi Clarence ,

Something like this ?

Narayan
Hello,

Yes, this is a better approach than just seeing the ranges. I still have a question about all those cells being on the spread sheet. Do you see a better interface approach to input this information? I am not sure how this will go over with the users. Are there some good example of user interface created in excel?
 
Hello,

Yes, this is a better approach than just seeing the ranges. I still have a question about all those cells being on the spread sheet. Do you see a better interface approach to input this information? I am not sure how this will go over with the users. Are there some good example of user interface created in excel?

I just was looking at my code and I see that I have some code place in those sections that you are using.
 
I just was looking at my code and I see that I have some code place in those sections that you are using.
The reason I mention that have I have code in those section. I am creating my solution off of the example at contextures. They use the same section to do some checking for information. I am not sure how smooth I can incorporate this into the code. I hope that I do not have to go back to the drawing board and scrape everything.

http://www.contextures.com/exceldataentryupdateform.html
 
Hi Clarence ,

Can you tell me which of the 3 versions available on your posted link you are using ? I can then go through the code , and see how what is posted there can be integrated with what is now in the uploaded file.

Narayan
 
Hi Clarence ,

Can you tell me which of the 3 versions available on your posted link you are using ? I can then go through the code , and see how what is posted there can be integrated with what is now in the uploaded file.

Narayan
I will send you a version of what I am working within about 10 hours from my time here 5:53 a.m.
 
Hi Clarence ,

That will be great.

Narayan
Here is my actual form with the problems. How to make sure that the option columns can work. How to error proof the and make the entry of data quick.

On the input tab, the data is being saved to the L column instead of D. The J column is to be a lookup section that bring back past lessee information. I will need to use the LookupLists Tab information to do the search criteria. The lessee Name will not change. The list of Name will not grow any larger. I have an attach file image that showing the direction that I am going with this process. Can excel handle multiple user entering information. There are 32 Lessee with 40 cells of information to input. I am not sure how to handle the locking. I will be back online in about 10 hours from my time of 7:26 am. Note: I have a test Input Data tab with one set of data for inputting with correct results.
 

Attachments

  • ActualWorkBook090914.xlsm
    66.4 KB · Views: 3
  • MainMenuVisionOfSystem.png
    MainMenuVisionOfSystem.png
    38.6 KB · Views: 2
Back
Top