Dragging rows formulas linking to other files

Microsoft Office excel 2007 home & stude...
July 20, 2010 at 13:53:39
Specs: Windows XP
I'm setting up a excel sheet for everyday of the month(1 file per day) it will show daily sales but, I also want it to have a two month to day colums at the end. I understand how to add the sums from other files (=[01.xls]journal!$M$5+M5+[02.xls]journal!$M$5) I have 46 rows in each sheet, the problem is when I make the first formula and drag it down the only numbers that go up are the local file numbers (=[01.xls]journal!$M$4+M46+[02.xls]journal!$M$4), and because I have and extra 90 numbers to change for every day, by the time I get to the end of the month I will have to change 2700 things a day, also doing so much manual input I'm worried I might make mistakes. Does an easier way exist? Thanks very much for any help. I can email a copy of the sheet if it would help you understand.

See More: Dragging rows formulas linking to other files

Report •

July 20, 2010 at 17:00:50

Just to start with, you have a series of Workbooks, with one for every day of the month.
From what you have posted it looks like you have worksheets named "01.xls", "02.xls" and so on to "31.xls"

Each of these workbooks has one Worksheet named "journal"

Then you say I also want it to have a two month to day colums at the end

As you have 31 workbooks, which one has this extra information, or is this in a different Workbook, perhaps "MonthSummary.xls"

Can you explain what these two columns are, or is it just one column:
You call them "month to day columns"

You need to explain the formula you posted:

This refers to cells M5 on worksheets named "journal" in two workbooks - 01.xls and 02.xls
What is not clear is how the middle M5 relates to these other two cells.

I am quessing that this formula is in the 03.xls workbook and in a worksheet named "journal" and that you are looking to get a total for "month to date".

In cell M5 on worksheet "journal" in workbook "04.xls" do you plan to have this formula:
=[01.xls]journal!$M$5+[02.xls]journal!$M$5+ [03.xls]journal!$M$5+ M5

Regarding your problem with dragging the formula, you have mixed relative and absolute addressing.

Try this in a new worksheet:
enter =A1 in cell B1.
Drag it down one row to B2 and the formula will be =A2
Drag it again from B1 to C1 and the formula will be =B1

In other words the address is relative to where it has been dragged to.

Now enter =$A$1 in cell B1
Drag it down to cell B1, then drag it again from B1 to C1
Both formulas will be =$A$1

In this case the address was 'absolute' - it did not change.

As you want you address to change as you drag it, don't use the $ signs.
=[01.xls]journal!M5+[02.xls]journal!M5+ [03.xls]journal!M5+ M5
is likely what you are looking for.

Using a new workbook for every day seems to be rather complex.
If you want to add 31 days data, your formula will be very long !

If you had one Workbook, with one worksheet per month and one column per day in each monthly worksheet, you could have the total for a month as =SUM(A5:AE35)
(column A=1, column AE=31)

Total to date for the 3rd day would be SUM($A5:C5)
Drag it one column right and you get the Total to date for the fourth day SUM($A5:D5)
The column part of the address ($A) is absolute and does not change when dragged to the next column and the column C part is relative and changes as it is dragged.
Both parts are relative and if dragged down to the next column it becomes:


Report •
Related Solutions

Ask Question