stuartmacdonald
New Member
- Joined
- May 26, 2009
- Messages
- 48
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)
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)