Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating external references to cells, sheets etc.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
updating external references to cells, sheets etc.
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) "Martin Fishlock" wrote in message ... 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. "dd" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel transpose and references to cells in other sheets | Excel Discussion (Misc queries) | |||
Updating references | Excel Programming | |||
inserting sheets into existing workbooks w/o external references | Excel Worksheet Functions | |||
External References | Excel Discussion (Misc queries) | |||
Updating Sheets on a non continuous cells | Excel Programming |