Formula too long! Summing 16 workbooks

[stuartmacdonald]

Hi

Im trying to sum 16 different workbooks witha simple formula, but it exceeds the maximum character length. This is the formula I want to repeat for 16 work books:

=SUM('H:\CERN\Timesheets\2009-2010\[Adrian Nolan 2009-2010.xls]Jul'!$H$46

As you can see, half the formula is the file location! Is there anyway I can develop a formula to examine a range of workbooks in a set location? VBA looks the way to go but I dont have clue where to start! Any help greatly appreciated!



Full formula:


****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/STUART%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>=SUM('H:\CERN\Timesheets\2009-2010\[Adrian Nolan 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Francis Markus 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Gareth Brown 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Ian Filbin 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[James Evans 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Jessica Smith 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Jessica Arnold 2009-2010.xls]Jul'!$H$46+ 'H:\CERN\Timesheets\2009-2010\[Jon Breeze 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Matthew Jackson 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Natalie Qureshi 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Paul McKenna 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Richard Muscat 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Rupert Greenhalgh 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Sarah Longlands 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Stuart MacDonald 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Victoria Bradford 2009-2010.xls]Jul'!$H$46)

 

[Kaps_mr2]

I haven't looked at the workbooks yet - but a thought is coming into my mind. These files are all time dependent - i.e they depend on months & years. What happens when the timeframes in the model expire ? e.g the model contains data (and links) up until Dec 09, what happens in Jan 10 ?

or am I being stupid ? Thanks

Kaps

 

[stuartmacdonald]

The timesheets are set up for a year (July 2009 - June 2010) for our financial year. Previously I have just copied the last years master sheets, and amended the dates, however this year we have more people using them and as a result the formulas are too long in the monthly analysis section.

I have tried using a named range to reduce the length of the original
formula, which did look like this:

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/STUART%7E1/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>=SUM('H:\CERN\Timesheets\2009-2010\ [Adrian Nolan 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Francis Markus 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Gareth Brown 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Ian Filbin 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[James Evans 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Jessica Smith 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Jessica Arnold 2009-2010.xls]Jul'!$H$46+ 'H:\CERN\Timesheets\2009-2010\[Jon Breeze 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Matthew Jackson 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Natalie Qureshi 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Paul McKenna 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Richard Muscat 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Rupert Greenhalgh 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Sarah Longlands 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Stuart MacDonald 2009-2010.xls]Jul'!$H$46+'H:\CERN\Timesheets\2009-2010\[Victoria Bradford 2009-2010.xls]Jul'!$H$46)

I reduced it to this, but I did not include the cell ref in the named range as advised, thinking I could use it over and over like this:

=[Adrian]Jul!$E50+[Francis]Jul!$E50+[Gareth]Jul!$E50+[Ian]Jul!$E50+[James]Jul!$E50+[Jessica]Jul!$E50+[Jess]Jul!$E50+[Jon]Jul!$E50+[Matthew]Jul!$E50+[Natalie]Jul!$E50+[Paul]Jul!$E50+[Richard]Jul!$E50+[Rupert]Jul!$E50+[Sarah]Jul!$E50+[Stuart]Jul!$E50+[Vic]Jul!$E50

=[Adrian]Jul!$E51+[Francis]Jul!$E51+[Gareth]Jul!$E51+[Ian]Jul!$E51+[James]Jul!$E51+[Jessica]Jul!$E51+[Jess]Jul!$E51+[Jon]Jul!$E51+[Matthew]Jul!$E51+[Natalie]Jul!$E51+[Paul]Jul!$E51+[Richard]Jul!$E51+[Rupert]Jul!$E51+[Sarah]Jul!$E51+[Stuart]Jul!$E51+[Vic]Jul!$E51

However, everytime I change the cell ref in the formula, excel asks me to locate the file for each individual named range, for each cell!

Got to be an easier way given I want to drag this formula down columns!

 

[RoryA]

Given the layout you have to work with, I would add a table to each of the individual's worksheets in the summary workbook with links to the relevant cells in that individual's detail timesheet. Then sum those tables on the Overview sheet. I can't think of anything that would be easier than that.

 

[Kaps_mr2]

Don't implement this until you get a reply from RoryA & Domski and the others (all of whom will come back with a better suggestion) but as starter for ten I would set up a workbook that has :-

1) A single cell that refers to the financial year e.g 2009- 2010.
2) Across a single row the months of the year Jan, Feb, Mar etc
3) Down the left hand side a column with everybody's names

then concatenate these into a string and use INDIRECT.

Finally have rows / columns to sum and give you totals.

You can then change 2009-2010 to 2010-2011 easily rather than rebuilding the workbook every year. Thanks

Kaps

 

[Domski]

I think the OP's main concern with using INDIRECT was the fact that it doesn't work with closed workbooks.

One way round this would be the use the INDIRECT.EXT function that comes with the MoreFunc add-in. Anyone using the summary workbook would need to have the add-in installed but it's an option.

You can download it from here: http://xcell05.free.fr/morefunc/english/

Dom

 

[stuartmacdonald]

Thank you all very much for your help and support today, I think the way forward is to re layout the summary sheet so it doesnt need major rework each year - I just need to find the time to do it!

Again many thanks