how do i avoid the #ref! error?

On my spreadsheet I have a full roster of all the employees at my place of business. I use that roster to populate other pages in the workbook with the simple point to formula. ( ='roster page'!A3)

The problem with that is if I change the roster by deleting people or adding people I sometimes get #ref! error on other pages since the source cell that the error cell is now pointing to is empty. Is there any way I can have an update able roster on one page and not have to worry about errors spearing on other pages?

Also =INDIRECT("'roster page'!A3") ( which is volatile)

Without knowing for what purpose you are referencing the employees, it's hard to tell what the root problem is... but, the way you are currently set up you are not technically referencing the employee directly; rather, you are referencing the cell the employee's name is currently in. So, deleting a name from the roster, adding a name, or even sorting the list alphabetically could potentially create multiple issues elsewhere in your sheet, assigning incorrect employee's names to other areas of your workbook.

If it were my project, I would assign every employee a unique employee number, in column A of the roster. I would then list employees in column B. To reference employees elsewhere, I would use a vlookup formula to search the roster for an employee number in column A, and return the employee name from column B. In this case, I would not delete employees as they leave the company, but replace their name with "INACTIVE EMPLOYEE", so that any reference to that employee would now read "INACTIVE EMPLOYEE" rather than "#ref!". You would then know you need to reassign an active employee to whatever that reference was attached to.


You could also then get fancy, and add a third column to the roster for status, and fill that column with "Active" or "Inactive". Then, you could use a second vlookup inside an IF statement to determine if the referenced employee is still with the company, and if their status is "Inactive", return something other than the name to signal that the employee is inactive. This would allow you to maintain the full list of present and former employees, for reference, and if you need to see the full current list only, just filter column 3 to show only "Active" employees.

If this sounds useful, let me know and I will throw together a sample example file. If not, and one of the above solutions was sufficient, then please mark the thread as "solved".

Thanks!

Originally Posted by Arnold Layne

If this sounds useful, let me know and I will throw together a sample example file.

As long as it is done according to forum rule #8

Originally Posted by Pepe Le Mokko

As long as it is done according to forum rule #8

Of course, I'd post it here in the thread. Just didn't want to waste the time if the OP's issue was already solved.

Thanks Arnold