updating external references to cells, sheets etc.

[dd]

I'm trying to create a summary sheet showing the total time spent, on a
weekly basis, at varoius sites. The summary sheet is the same layout and
format as the timesheets, so that the totals of all sheets for cell C14 is
shown in cell C14 on the summary sheet.

So for the sites Cathcart, Howwood, KyleofLochalsh, Lanark and Tain and date
151206 I have the following function.

=SUM('P:\\Time Booked\[CathcartTime.xls]151206'!$C$14,'P:\\Time
Booked\[HowwoodTime.xls]151206'!$C$14,'P:\\Time
Booked\[KyleofLochalshTime.xls]151206'!$C$14,'P:\\Time
Booked\[LanarkTime.xls]151206'!$C$14,'P:\\Time
Booked\[TainTime.xls]151206'!$C$14)

The workbooks are named by Site and the Worksheets within each book are
named by Week i.e.
Workbooks: CathcartTime.xls, HowoodTime.xls etc.
Worksheets: 221206, 050107, etc

The above function totals the time from the file/sheet/cells stated, but
when I try to copy this to other cells it doesn't update the reference to
reflect, for example, the totals for cell C15.
Is there an easy way to update this to reflect totals for cells in the range
C14 to T23?
Is there a way to update this to reflect time in the worksheets dated
221206, 050107, 120107, 190107 and 260107?

Regards
Dylan Dawson
Scotland

 

[dd]

Thanks Martin

I forgot about that.

I've never used the direct function before, I was close to using it once
before, but copped out by using hyperlinks to open the required worksheet.

My workbook contains a worksheet for each date required.

I wonder if I could write a macro for the selected cell which would include
a file selection box which will let me select a number of files and make the
worksheet field equal the name of the current worksheet. How would I then
sum the selected cell from each worksheet in each workbook?

Maybe a For Each workbook statement to select all files in the directory and
an If worksheet name equals statement which picks up the name of the target
worksheet. Would this work with the files closed though?

=SUM('P:\\Time Booked\[CathcartTime.xls]151206'!$C$14,'P:\\Time
Booked\[HowwoodTime.xls]151206'!$C$14,'P:\\Time
Booked\[KyleofLochalshTime.xls]151206'!$C$14,'P:\\Time
Booked\[LanarkTime.xls]151206'!$C$14,'P:\\Time
Booked\[TainTime.xls]151206'!$C$14)

Dylan:

Remove the $ from the cell addresses and you can them copy.

The $ makes the addresses fixed and this is the default for excel linking to
other sheets.

Regarding the sheets you can try indirect but the files have to be open for
direct to work or write a rountine that change the dates this could be done
with a data validation dropdown and a change event.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

I'm trying to create a summary sheet showing the total time spent, on a
weekly basis, at varoius sites. The summary sheet is the same layout and
format as the timesheets, so that the totals of all sheets for cell C14 is
shown in cell C14 on the summary sheet.

So for the sites Cathcart, Howwood, KyleofLochalsh, Lanark and Tain and
date
151206 I have the following function.

=SUM('P:\\Time Booked\[CathcartTime.xls]151206'!$C$14,'P:\\Time
Booked\[HowwoodTime.xls]151206'!$C$14,'P:\\Time
Booked\[KyleofLochalshTime.xls]151206'!$C$14,'P:\\Time
Booked\[LanarkTime.xls]151206'!$C$14,'P:\\Time
Booked\[TainTime.xls]151206'!$C$14)

The workbooks are named by Site and the Worksheets within each book are
named by Week i.e.
Workbooks: CathcartTime.xls, HowoodTime.xls etc.
Worksheets: 221206, 050107, etc

The above function totals the time from the file/sheet/cells stated, but
when I try to copy this to other cells it doesn't update the reference to
reflect, for example, the totals for cell C15.
Is there an easy way to update this to reflect totals for cells in the
range
C14 to T23?
Is there a way to update this to reflect time in the worksheets dated
221206, 050107, 120107, 190107 and 260107?

Regards
Dylan Dawson
Scotland