# Workbook to calculate totals

#### shaunt1

##### New Member
I have an excel workbook for everyday that has statistical data, the file names are Downtime 030109.xls. I want to make another workbook(named Total) that will import the data from each days workbook. The problem I am having is that on the Total workbook when I click the small plus in the text box and drag to copy the formula. The file date does not change. I need the date to change so I don't have to correct on each formula, each month.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Jubjab

##### Well-known Member
You have several options:

1) Type the first 2 dates, i.e. in cell A1 1/1/2009 and in A2 2/1/2009. Then select both cells and drag with the plus sign. The dates should form up nicely.

2) Type the date in A1, then in cell A2 type the formula
Code:
``=A1``
Then copy and paste that formula down.

Option 2 has the benefit that you only need to update the first cell for all dates to update when you are switching to a new month.

#### shaunt1

##### New Member
I have a separate work book for each day and I have one work book that will input only certain data from each daily work book. The problem I am having is that on the monthly sheet the formula for A1 looks like =c:\downtime logs\March\'[downtime 030109.xls]airline errors'!\$B\$3. The monthly sheet A2 should be '[downtime 030209.xls]airline errors'!\$B\$3. Is there a simple way to copy these formulas where the date will increment for you so I don't have to change the date on each formula?

#### Jubjab

##### Well-known Member
My first though was to use INDIRECT, but it doesn't work with closed workbooks. So I think you need a macro approach:

Code:
``````Sub a()

monthnumber = Range("b1").Value
mnthname = MonthName(Range("b1").Value)
days = Range("d1").Value

For i = 1 To days
If i < 10 Then d = "0" & i Else d = i
If monthnumber < 10 Then m = "0" & monthnumber
Cells(i + 1, 1).Formula = "'=c:\downtime logs\" & mnthname & "\'[downtime " & d & m & "09.xls]airline errors'!\$B\$3"
Next i

End Sub``````

Paste the above code into a normal module and run the macro. Note: delete the apostrophe (') from the macro formula if the path is correct. I.e. change the row

"'=c:\downtime logs\" & mnthname & "\'[downtime " & d & m & "09.xls]airline errors'!\$B\$3"

to

"=c:\downtime logs\" & mnthname & "\'[downtime " & d & m & "09.xls]airline errors'!\$B\$3"

#### Jubjab

##### Well-known Member
Note: you need to setup the number of days and current month on row 1, like this:
Työkirja1
ABCDEFG
1Month:3Days in month:31
2=c:\downtime logs\March\'[downtime 010309.xls]airline errors'!\$B\$3
3=c:\downtime logs\March\'[downtime 020309.xls]airline errors'!\$B\$3
4=c:\downtime logs\March\'[downtime 030309.xls]airline errors'!\$B\$3
5=c:\downtime logs\March\'[downtime 040309.xls]airline errors'!\$B\$3
6=c:\downtime logs\March\'[downtime 050309.xls]airline errors'!\$B\$3
7=c:\downtime logs\March\'[downtime 060309.xls]airline errors'!\$B\$3
8=c:\downtime logs\March\'[downtime 070309.xls]airline errors'!\$B\$3
9=c:\downtime logs\March\'[downtime 080309.xls]airline errors'!\$B\$3
10=c:\downtime logs\March\'[downtime 090309.xls]airline errors'!\$B\$3
11=c:\downtime logs\March\'[downtime 100309.xls]airline errors'!\$B\$3
12=c:\downtime logs\March\'[downtime 110309.xls]airline errors'!\$B\$3
13=c:\downtime logs\March\'[downtime 120309.xls]airline errors'!\$B\$3
14=c:\downtime logs\March\'[downtime 130309.xls]airline errors'!\$B\$3
15=c:\downtime logs\March\'[downtime 140309.xls]airline errors'!\$B\$3
16=c:\downtime logs\March\'[downtime 150309.xls]airline errors'!\$B\$3
17=c:\downtime logs\March\'[downtime 160309.xls]airline errors'!\$B\$3
18=c:\downtime logs\March\'[downtime 170309.xls]airline errors'!\$B\$3
19=c:\downtime logs\March\'[downtime 180309.xls]airline errors'!\$B\$3
20=c:\downtime logs\March\'[downtime 190309.xls]airline errors'!\$B\$3
21=c:\downtime logs\March\'[downtime 200309.xls]airline errors'!\$B\$3
22=c:\downtime logs\March\'[downtime 210309.xls]airline errors'!\$B\$3
23=c:\downtime logs\March\'[downtime 220309.xls]airline errors'!\$B\$3
24=c:\downtime logs\March\'[downtime 230309.xls]airline errors'!\$B\$3
25=c:\downtime logs\March\'[downtime 240309.xls]airline errors'!\$B\$3
26=c:\downtime logs\March\'[downtime 250309.xls]airline errors'!\$B\$3
27=c:\downtime logs\March\'[downtime 260309.xls]airline errors'!\$B\$3
28=c:\downtime logs\March\'[downtime 270309.xls]airline errors'!\$B\$3
29=c:\downtime logs\March\'[downtime 280309.xls]airline errors'!\$B\$3
30=c:\downtime logs\March\'[downtime 290309.xls]airline errors'!\$B\$3
31=c:\downtime logs\March\'[downtime 300309.xls]airline errors'!\$B\$3
32=c:\downtime logs\March\'[downtime 310309.xls]airline errors'!\$B\$3
33
Taul2

#### shaunt1

##### New Member
Jubjab
This macro works great. I am so glad you took the time to assist me in solving this problem. I can say enough how much this will help me. Thanks a million!ray:

Replies
3
Views
312
Replies
2
Views
163
Replies
0
Views
415
Replies
0
Views
214
Replies
0
Views
330

1,191,287
Messages
5,985,757
Members
439,979
Latest member
alekun86

### 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.

### Which adblocker are you using?

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

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