Hey there. I'm on work experience in Ford at the moment.

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

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

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

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

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

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

So, is there a way to turn off the error checking that displays a 'Fil
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 hel
given. I'm leaving the office on friday and i'm so close to finishin
this project for them :).

Thanks!

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

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/