Automation help

[shaneh]

I have report workbook that I need to build out for a 2 year period.
Each tab of the sheet is for a new week, and all tabs have duplicate layouts.
In each sheet, there are cells that pull data from other workbooks. Not all of these workbooks are in the same folder.

An example would be:


B10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$16
C10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$17
D10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$18
E10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$19
F10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$20
G10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$21
H10: ='X:[Maint Daily Tracking 2005.xls]February 2005'!$I$22

The next tab for the next week, these cells look like:

B10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$23
C10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$24
D10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$25
E10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$26
F10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$27
G10: ='X:\[Maint Daily Tracking 2005.xls]February 2005'!$I$28
H10: ='X:[Maint Daily Tracking 2005.xls]February 2005'!$I$29


Pretty simple to make a new tab and change the values of each cell to reference. However, when building this workbook out for each week for two years, it is time consuming. Especially since I am doing something like this for rows 2-50 on each tab.

Is there any way to automate this so I can insert a formula into these cells that would automatically insert the value in the next row from the other worksheet?

I apologize if i am not being clear enough.

 

[BrianB]

The best method is to have a single data table with a column for "Day". This would save having problems such as this one - as well as worse ones when you come to analyse the data. Excel would then be able to do all the hard work.