I've made an excel workbook which has 3 sheets.
- One sheet for entering data in rows, with columns such as: Name, state, join date, days used on training etc.
- 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.
- 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.