+ Reply to Thread
Results 1 to 8 of 8

How to reference to worksheets that don't yet exist, but avoiding the #REF! error message

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    How to reference to worksheets that don't yet exist, but avoiding the #REF! error message

    Firstly, apologies if this is rather garbled and long winded, I'm not familiar with the correct terminology but will explain the best I can.

    Basically, my problem is that when I try and include a link to a different worksheet that does not (and cannot) yet exist, part of the formula is replaced with #REF! Is it possible to prevent this?

    The background is: We have 6 assessors and each be assessing around 20 applicants, on the answers to 25 questions. Each assessor will have their own workbook.

    On the day the applications arrive, I'll need 6 No workbooks (one for each assessor), each one containing 20 No worksheets (one for each applicant) with each worksheet being the name of the applicant. The values from all of the questions on each assessor's worksheets will be averaged and feed into a master workbook.

    Unfortunately we won't know the names of the companies in advance, so I can't name the worksheets in advance, and so I can't populate the formulas in advance.

    Does anyone know a way around this?

    EDITED TO ADD: This is for a rather paranoid employer, so I don't have admin rights for uploading any additional software or macros. Basically, I need a solution that works within the existing Excel 2007 platform.

    EDITED AGAIN TO ADD: While I won't know the names of the applicants until the last minute, or even the numbers, I did wonder about creating the worksheets and formulas anyway, using names like (eg) flopsy, mopsy and cottontail &c. and then doing a find and replace to replace all references to "flopsy" with "A Serious Company" when we know who the applicants are. Would this work?
    Last edited by Haylo; 07-10-2012 at 08:34 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    Ask your employer to read this link The God Particle: If the Universe Is the Answer, What Is the Question?., you never know s/he might be brighter than you think! ...

    Run that past me again, I'm missing something ...

  3. #3
    Registered User
    Join Date
    04-20-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2013, 2010, 2007, 2003
    Posts
    99

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    Try this:
    Put your list of names in cell A1 going down.
    Remember that 2 worksheets can't have the same name...


    Please Login or Register  to view this content.
    SPARTAN
    Please click the * if my solution helped

  4. #4
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    Any reason why you can't just set up the sheet using the default sheet1, sheet2, etc?

    When you change the tab name from sheet1 to company123, any formula referring to sheet1 will update to reflect the change automatically.

  5. #5
    Registered User
    Join Date
    07-10-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    singerJ6, thanks but that's beyond me, I'm afraid I've no idea what to do with that code!

    jason.b75, that tip is really useful, but only seems to work within one workbook, I have inherited two master sheets, each one having cells that are referenced to the other.

    I have a "scoring" workbook that goes to each person doing the scoring, it has a worksheet for each company, the results from which feed into the summary workbook.

    The "summary" workbook also has a worksheet for each company, where all the information from all the individual scoring workbooks are gathered together. To complicate matters, several items are scored centrally, so those are entered directly into the summary workbook - and the values appear for information in the scoring workbooks.

    Not the way I'd have set it up, but this is the system I've inherited.

    Does excel have a "Yes I know those formulas don't lead anywhere right now, but they will, so stop forcing me to update them!" setting?
    Last edited by Haylo; 07-11-2012 at 12:50 PM. Reason: To highlight my question, among all the garbled explanations!

  6. #6
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    Quote Originally Posted by Haylo View Post
    Does excel have a "Yes I know those formulas don't lead anywhere right now, but they will, so stop forcing me to update them!" setting?
    I have an answer, but unfortunately not the one you want to hear http://support.microsoft.com/kb/826921

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,273

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    You can use INDIRECT but then the workbooks need to be opened in order for the links to work. Would they be opened? You could download and active the add in morefunc which includes a function INDIRECT.EXT which works with closed workbooks.
    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

  8. #8
    Registered User
    Join Date
    07-10-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to reference to worksheets that don't yet exist, but avoiding the #REF! error mess

    Quote Originally Posted by jason.b75 View Post
    I have an answer, but unfortunately not the one you want to hear http://support.microsoft.com/kb/826921
    Well, it's not the answer I wanted to hear, but it's the one I needed to know so thanks for that.

    I do appreciate the help, the trouble with being self taught is that while you can figure out relatively complicated stuff if you try hard enough, sometimes the totally obvious eludes you.

    Quote Originally Posted by ChemistB View Post
    You can use INDIRECT but then the workbooks need to be opened in order for the links to work. Would they be opened? You could download and active the add in morefunc which includes a function INDIRECT.EXT which works with closed workbooks.
    ChemistB, not only would they not both be open, I've just learned that for this procurement not all the users have access to the directory in which both sheets will be stored, so it looks as if a bit of a re-think is in order.

    Spreadsheets 101 - Lesson 1. Create your own from scratch, it's quicker in the end than trying to shoehorn someone else's into your application.
    Will I never learn?

+ 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