Master Summary sheet to "remember" formulae and not do "REF!" errors? (EXCEL 2003)

Hoping someone can guide me...

This is for Excel 2003 (not the more recent ones sadly!)

I have 6 identical workbooks, one for each team. Each workbook has multiple worksheets. The important worksheets for this query are the indvidual StaffTimeRecording sheets (one for each team member) which spans an entire week (of 5 days) and the TeamSummary sheet which pulls from each team members worksheet to create totals for that week.

I have created an all department version of this workbook. A version of this summary sheet has been created (AllDeptSummary) to total up all the TeamSummary sheets to create a weekly snapshot of work done for the entire department.

I have found some lovely VBA code which will:
1. Copy the TeamSummary worksheet from each team's workbook into the AllDeptWorkbook - this code works!
2. Rename each TeamSummary worksheet tab with its own Team Name (Eg Team 1, Team 2 etc) - this code works!
3. Ensure all formulae linking back to the original sheets become Values only in the new workbook - this code works.

What I need is to have the AllDeptWorkbook as a master file initially which kind of "remembers" the formulae even if the worksheets its looking at aren't "there" but will be when they get copied in and renamed (the naming will be the same week on week, the positions of everything on each worksheet, will remain the same as the worksheets are identical, the only difference is the filename will change to reflect the previous weeks' date). The problem is that even if I set up the first one manually and get the formulae correct, once those workbooks are removed (for the Master version), the formulae will (understandably) break! I get REF! errors.

Is there a way to ensure the Master version "retains" or "remembers" the formulae in readiness for the specific (and correctly named) worksheets being copied in? Its not at all possible to manually update the formulae each week as the AllDeptSummary page is VERY long (covers an entire week).

Any suggestions/pointers/help will be gratefully received. Thankyou for your time. (Apologies if this is in the wrong place...not sure if its VBA or formulae solution).

Hi, and welcome to the forum.

It's a little difficult to visualise precisely what you're getting at without seeing examples of your workbooks. However if I understand correctly you have a master workbook which has formulae that link to other workbooks, and you want the master workbook to be updated each week when you have a new set of weekly workbooks.

If that's the case and the information on each weekly workbook that you're linking to is in exactly the same position then just use the Date Edit Links functionality and point to the new source workbook. 2003 is a long time ago but as far as I recall the Edit links functionailty is in there too.

Just a general point but for situations like this where summary information is needed it's not good practice to distribute data across several workbooks. Ideally you should have one workbook and one sheet that contains all the data in a proper 'normalised' two dimensional table. You'd need to add additional columns to hold the date fo a record (if you're not already doing so, and another column to hold the Team reference. How records are added to the single sheet database will depend on your business process.

I realise that this is partially what you're trying to achieve with the copying and pasting macros but it would be better to capture the records in a database table in the first instance.