trouble with closed workbook

[Bob L]

I have a few spreadsheets that are linked to other workbooks. Everyone says
that you cannot get data from a closed workbook. Here is what I have. I
have workbook called Journal. In that I have cells that have data in them
and are totaled at the bottom of the sheet. I have 4 other workbooks called
Quarter's 1 through 4. Once I put the data in the journal and save and
close it the data is transferred to the appropriate cells in the workbooks.
Even if the journal is closed the quarter still has the data in the cells.
I have another one called Budget. That is linked to the quarter workbooks.
The only way that it shows the totals in the workbooks is if the Quarter
workbooks are open. Why do the quarter workbooks collect their data from a
closed Journal but the budget will not get its data from the quarter books?

 

[Roger Govier]

Hi Bob

What formulae are being used in each case?

 

[Bob L]

Roger,

In the journal the only formulas are the ones that total each cell, so it is
just =sum. The quarter worksheet that seems to collect data from the closed
journal workbook the formula is =INDEX('M:\[journal.xls]July
2010'!B$66:W$66,ROWS(B$11:B13)). I use to have
=OFFSET('C:\Users\Bob\Documents\Budget\[journal.xls]July
2010'!$B$66,0,ROW(A1)-1), but that gave me an #value error in each cell.
You'll notice that in the offset one it is looking at a different directory.
That is only because I had to get it from my documents so I could see what
the formula is. The other ones come from my flash drive, the budget
worksheet is also on the flash drive with the INDEX formula. The reason that
I have the formulas like they are is because I did not want to fill each
cell one at a time so I used the fill handle to copy the formulas.

In the budget sheet the formula is just ='M:\[quarter1.xlsx]Quarter 1'!$E13.
E14,E15 and so on.

I hope I didn't confuse you.

Thanks,
Bob