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?
Bookmarks