+ Reply to Thread
Results 1 to 7 of 7

how do i avoid the #ref! error?

  1. #1
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    2010
    Posts
    251

    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?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,436

    Re: how do i avoid the #ref! error?

    In B1 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,622

    Re: how do i avoid the #ref! error?

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

  4. #4
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: how do i avoid the #ref! error?

    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!

  5. #5
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,622

    Re: how do i avoid the #ref! error?

    Quote Originally Posted by Arnold Layne View Post

    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

  6. #6
    Forum Contributor
    Join Date
    10-01-2018
    Location
    Virginia Beach, VA
    MS-Off Ver
    Excel 2013
    Posts
    118

    Re: how do i avoid the #ref! error?

    Quote Originally Posted by Pepe Le Mokko View Post
    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.

  7. #7
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,622

    Re: how do i avoid the #ref! error?

    Thanks Arnold

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1