Automatically updating cells in one Excel file to another

[Col]

Hi All,

I'm currently updating a project around staff attendance that I'm doing in
Excel which will then go out to all of my offices.

The thing is that as we are now a couple of weeks or so into the financial
year I wanted to save my office managers the task of inputting all their
staff's start and finish times over again.

If the original file is called Staff1.xls and the new one Staff2.xls, is
there a formula I could put in say E4 of the new one (Staff2.xls) which
would check to see if the other file (Staff1.xls) was open and if so copy
over the details.

I know I can do a straight cell lookup but I wanted to combine this with an
'IF' function so if the file wasn't open then that particular cell could do
something else like point to another cell within Staff2.xls or do a
calculation.

Reason for this is that all the managers have not completed their attendance
records to date and currently those particular cells point to the previous
day's attendance, meaning a staff's attendance time could be input on the
first Monday of the financial year and those attendance times will then flow
through until the year end, so for the managers it means less inputting for
those staff on a fixed duty time.

Any help greatly appreciated,

Colin.

 

[Ross Culver]

Col,

I can't say that I completely understand what you're after, but have you
considered writing a procedure in VBA to accomplish this? If the file names
and paths are consistent as well as the key values (such as staff ID, mgr
ID, etc.) then a VBA macro would be just the ticket.

Ross