Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry + Validation
Hi There,
1. Can someone give me suggestion about data entry on a huge Excel Spreadsheet? What is the best approach to this? I often found that user always got confused in this rows & rows and columns & columns of spreadsheet that they need to enter some data? What would you do to clear the confusion? 2. Is there a way to do a Data Validation that will validate the data for user to input an "X" only in one cell out of the three cells in a row? This is a data collection process after a survey. Ex: there are 3 answers to a question, and only one answer should be selected. Is there a way to validate this in Excel. Please keep in mind that there are more than one questions, and each question has different set of answers. Thank you, Neon520 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry + Validation
Hi,
I solve the same problem with an userform look into the link attached it will give you a pretty good idea http://contextures.com/xlUserForm01.html http://word.mvps.org/FAQs/Customizat...nTheBlanks.htm http://www.fontstuff.com/ebooks/free/fsUserForms.pdf Hope this will help "Neon520" wrote: Hi There, 1. Can someone give me suggestion about data entry on a huge Excel Spreadsheet? What is the best approach to this? I often found that user always got confused in this rows & rows and columns & columns of spreadsheet that they need to enter some data? What would you do to clear the confusion? 2. Is there a way to do a Data Validation that will validate the data for user to input an "X" only in one cell out of the three cells in a row? This is a data collection process after a survey. Ex: there are 3 answers to a question, and only one answer should be selected. Is there a way to validate this in Excel. Please keep in mind that there are more than one questions, and each question has different set of answers. Thank you, Neon520 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry + Validation
1. Does the user only need to enter data in a small/limited amount of cells?
You could try protect sheet, but leave data cells unprotected. User can then use TAB to quickly go from cell to cell. Other than that, we'd need you to better describe what you want. 2. You can do this with a helper column. Assume answer cells are B:D, helper column is E. In E2, input this: =IF(COUNTIF(B2:D2,"X")<1,"X","") Now, select cells B2:D2, data-validation, select list, and choose cell E2 You can then copy these cells down as needed, and eventually hide column E, for the sake of aethetics. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Neon520" wrote: Hi There, 1. Can someone give me suggestion about data entry on a huge Excel Spreadsheet? What is the best approach to this? I often found that user always got confused in this rows & rows and columns & columns of spreadsheet that they need to enter some data? What would you do to clear the confusion? 2. Is there a way to do a Data Validation that will validate the data for user to input an "X" only in one cell out of the three cells in a row? This is a data collection process after a survey. Ex: there are 3 answers to a question, and only one answer should be selected. Is there a way to validate this in Excel. Please keep in mind that there are more than one questions, and each question has different set of answers. Thank you, Neon520 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Entry + Validation
Hi Luke M.,
Thanks for your response. 1. That's what I've tried before, but what I learn is, without the validation mechanism, some users end up struggling with the Tabbing once they tab too fast and putting "X" on the wrong column, need to delete and put the "X" back in the right cell. All the users need to input is "X" in each cell. I think this confusion partly contribute to the size of the sheet. 2. Does this mean that I need to have x amount of helper columns of x amount of questions in the survey to be input the data? This seem to be a lot of tedious work once the questions reach hundreds of them? Thanks, Neon520 "Luke M" wrote: 1. Does the user only need to enter data in a small/limited amount of cells? You could try protect sheet, but leave data cells unprotected. User can then use TAB to quickly go from cell to cell. Other than that, we'd need you to better describe what you want. 2. You can do this with a helper column. Assume answer cells are B:D, helper column is E. In E2, input this: =IF(COUNTIF(B2:D2,"X")<1,"X","") Now, select cells B2:D2, data-validation, select list, and choose cell E2 You can then copy these cells down as needed, and eventually hide column E, for the sake of aethetics. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Neon520" wrote: Hi There, 1. Can someone give me suggestion about data entry on a huge Excel Spreadsheet? What is the best approach to this? I often found that user always got confused in this rows & rows and columns & columns of spreadsheet that they need to enter some data? What would you do to clear the confusion? 2. Is there a way to do a Data Validation that will validate the data for user to input an "X" only in one cell out of the three cells in a row? This is a data collection process after a survey. Ex: there are 3 answers to a question, and only one answer should be selected. Is there a way to validate this in Excel. Please keep in mind that there are more than one questions, and each question has different set of answers. Thank you, Neon520 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to design data entry validation? | Excel Discussion (Misc queries) | |||
Macro Data Entry Validation | Excel Worksheet Functions | |||
Unique entry - Data Validation | Excel Discussion (Misc queries) | |||
Data Validation / Cell Entry | Excel Discussion (Misc queries) | |||
Combining conditions for data entry validation | Excel Worksheet Functions |