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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Workbook to calculate totals
- Thread starter shaunt1
- Start date
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
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.
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:
=A1Option 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.
Upvote
0
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?
Upvote
0
My first though was to use INDIRECT, but it doesn't work with closed workbooks. So I think you need a macro approach:
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"
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 SubPaste 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"
Upvote
0
Note: you need to setup the number of days and current month on row 1, like this:
| Työkirja1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Month: | 3 | Days 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 | |||||||||
Upvote
0
Similar threads
- Solved
