I've made an excel workbook which has 3 sheets.

  1. One sheet for entering data in rows, with columns such as: Name, state, join date, days used on training etc.
  2. Another sheet that takes input in dropdown menus and displays some performance indicators, enabling users to find the number of days people in a specific state have trained.
  3. The third sheet generates all the performance indicators. The sheet works simply by applying a bunch of IF statements to the cells in sheet one, while using the criteria input in sheet two. So for one performance indicator to be calculated, there will be a column of IF statements as long as there are row entries in sheet one, each of which will check whether the person "in its row" is from the state in question from sheet two and if so will return a number.

The sheet is a bit more complex, but the above is the essence. The problem is however, I keep getting #REFERENCE errors. Often I will be called over after a couple of days of use and all the performance indicators simply show #REFERENCE.

I believe this to be a issue with the users, but my users are at a low low Excel experience level. Is there anyway I can protect the sheet from these #REFERENCE errors better?

I have not been able to generate the performance indicators using Excel's built in functions and do not have any experience with VBA. :)

Hope you have some suggestions.


It sounds like you should take advantage of Excel's Workbook/Worksheet protection features to help maintain integrity. In Excel 2010 you can find the global options on the Review Tab.

By selecting "Protect Sheet" you are able to prevent people from performing some basic actions on the spreadsheet against any cell that is locked. (All cells start locked by default).

Anywhere you would expect the user to enter data, you will need to unlock by selecting the cell, right clicking and selecting 'Format Cells'. The option to unlock the cell is on the Protection tab.

Excel also offers Data validation (available on the Data Tab), which allows you to specify conditions to detect invalid data and provide a message to the user when they select the cell (non-intrusive) and one when they input invalid data (intrusive).

  • I've had a go with the protection features, but were not able to find a solution. The problem basically is that if data is moved around in the input sheet, a line is cut and pasted into another location. The IF statements will try to compensate, so if i move line 500 to 510. The corresponding line of IF statements will also change. IF statements referring to line 500 will refer to line 510 and the IF statements who referred to 510 wil now show the #REFERENCE error. – Simon Apr 9 '13 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.