Automation help

shaneh

New Member
Joined
Feb 12, 2005
Messages
4
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top