Hodges,

An easy and robust method would be to enter your formulas in the cells, but
put a single quote in front to enter them as strings. That way, Excel will
ignore them. Then in the workbook open event, look for the cells that should
have formulas linking them to the now existing workbooks and transform the
strings into working formulas. You could loop through the files, through
the cells, through the formulas, whatever, based on the current date.

HTH,
Bernie
MS Excel MVP

"HodGes " wrote in message
...
Hey there. I'm on work experience in Ford at the moment.

At ford, every month each employee has to fill in a timesheet, with
their overtime hours and days off etc, using a pre-made workbook in
excel. They then email this to their supervisor who prints it off and
copies it all into a master form, where he keeps all members days
off/overtime and totals them up each month.

My job is to create a workbook, consisting of 12 sheets, one for each
month. Across all twelve sheets there is a row for each member in the
supervisors group. These can be added and removed using macros.

There is 12 folders on the next level down to my main workbook in the
folder structure. /Jan, /Feb, /Mar etc. When the supervisor gets a
workbook via email, he can just drop it into these folders, and aslong
as it continues the right naming convention, my work book will
automaticly pull in all the information from these timesheets and total
them all up.

Now, my problem. I have an add member macro. Which adds 31x5x12 cells
that all look up to the right folder and file. The file name is a
variable and uses what is entered into the AddMember Form Macro. This
macro fills in all the cells with something similar to: \Jan-04\[Alan
Flower-Jan.xls]MONTHLY TIME SHEET'!$G$16.

Because the 'Alan Flower' varies, when the formula looks into a folder
and see's that the file is not there, it gives the user a 'File
Location' promt. I don't want this as the file will be dropped into the
folder when the month comes.

I tried to get around this by making the AddMember macro add emtpy
files to each of the folders with the right name to begin with. So that
my workbook would just lookup empty cells. But it isn't how i want to
do it as it increases the size of my folder, and i want to be able to
sent this around the office for other supervisors to use.

So, is there a way to turn off the error checking that displays a 'File
Location' box when a lookup formula can't find what it's looking up?


I'm sorry this was long, and i will really really appreachiate any help
given. I'm leaving the office on friday and i'm so close to finishing
this project for them :).

Thanks!!


---
Message posted from http://www.ExcelForum.com/