Pull info between workbooks

[Solomon King]

Excel 2000, Win2k OS.

Experience limited.

I have 2 workbooks, Daily and Weekly. Info (Incoming/Outgoing) gets entered into Daily, on a daily basis, with a new sheet for each day (1st, 2nd, etc). There are 12 "Daily" workbooks, one for each month of the year, each containing upto 31 sheets. Each Sheet contains 1 day - 1st is on one sheet, 2nd on the next, and so on. The sheet has a column with several relating rows, which contain details of where the income/outgoing has gone. Input goes into cells B4, B5 and B6, daily.

Weekly has 1 sheet, with 52 Week columns in it. Each Week column contains a "total" figure, for the corresponding weeks income/outgoing

I want Weekly to pull certain bits of info from Daily - 1st + 2nd + 3rd + 4th + 5th + 6th + 7th (a "week") in cells B4, B5 and B6, into Weekly, column "Week 1", cells B4, B5 and B6. Then, repeat this for every other Week column, moving to 7th + 8th + etc, with column "Week 2" being filled.

Could someone give me some pointers as to what formula I'd need to use to get it to do this? I think the problem lies with the fact that there are seperate files (all in the same directory). I think I can do it from sheets within the same book, but I dont know how to do it from across separate books.

Ive tried recording a macro, but Id rather not have macros in the end product.

Thanks for considering this matter.

[swatsp0p]

Assuming JanDaily.xls is the daily workbook for January, each sheet is named Day1, Day2, etc. and the output cell in your Weekly book is the same as you are reading from the daily:

In Weekly, cell B2 enter:
=C:\Documents and Settings\My Documents\DailyFiles\[JanDaily.xls]Day2!$B$4+[JanDaily.xls]Day3!$B$4+[JanDaily.xls]Day4!$B$4+[JanDaily.xls]Day5!$B$4+[JanDaily.xls]Day6!$B$4+[JanDaily.xls]Day7!$B$4+[JanDaily.xls]Day8!$B$4

note: The FULL path is required at the beginning of the formula, the file name is enclosed in braces [ ] , the sheet tab is followed by an exclamation point ! and the cell reference is made fixed by the inclusion of $

also note the Daily file does not need to be open to update the Weekly file.

HTH

[Solomon King]

Thanks for the advice.

Unfortunately, I get told that "there is an error in the formula" when I insert it into the relevant cell. At first, I realised that the location and filenames were wrong, so I changed them to the correct ones, still without any luck - same error message. Then I tried using just

=E:\Program Files\Excel Sheets\[January.xls]2nd!$B$4

as a formula in the cell. No error message this time, but no data was pulled round. All that was showing was the formula in the cell, despite there bing data in the corresponding cell in January.xls.

Any thoughts?

[swatsp0p]

If you are still seeing the formula in the cell after you press ENTER, I think it is treating the formula as a text entry. Verify there is not an apostrophe ( ' ) preceding the entry (check the formula bar at the top of the sheet).

And of course you need your formula to point to the exact location of YOUR file, my formula was an example only!

Good Luck