Data Entry + Validation

[Neon520]

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

 

[Eduardo]

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/Customization/FillinTheBlanks.htm
http://www.fontstuff.com/ebooks/free/fsUserForms.pdf

Hope this will help

 

[Luke M]

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, helper
column is E.
In E2, input this:
=IF(COUNTIF(B22,"X")<1,"X","")
Now, select cells B22, 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.

 

[Neon520]

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