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]

make A1 = "H:\CERN\Timesheets\2009-2010\"

and then replace all the instances of the text above with a reference to A1.

You may need to lock cells by using the dollar operator $ e.g $A$1.

You can combine strings using the "&" operator. Thanks

Kaps

 

[Jon von der Heyden]

make A1 = "H:\CERN\Timesheets\2009-2010\"

and then replace all the instances of the text above with a reference to A1.

You may need to lock cells by using the dollar operator $ e.g $A$1.

You can combine strings using the "&" operator. Thanks

Kaps

If you want to do that then you will need to use INDIRECT.EXT from the morefunc addin: http://xcell05.free.fr/morefunc/english/

 

[RoryA]

You can use INDIRECT (key part missing from Kaps' suggestion! ) to build up the formula string but it will only work when the other workbooks are open. How many cells are you trying to extract from each workbook?
I suspect you may need to rethink the whole structure of what you are doing - possibly using VBA to copy all the data into a summary sheet that you can refer to, or using ADO to query the other workbooks. Or even a database application, if you have one available.

 

[Kaps_mr2]

Thanks Chaps forgot about the INDIRECT part !!!

Kaps

 

[stuartmacdonald]

I have done this in previous years by just having two formulas, and then summing them together. I need this to work without the workbooks open.

I dont follow where I need to insert the INDIRECT!

 

[Kaps_mr2]

if you need this without the work books being open, then INDIRECT won't work.

You would simply enclose your entire expression into one big INDIRECT function :-

=INDIRECT("formulae")

Thanks

Kaps

 

[stuartmacdonald]

****** 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>I have set A1 to:

A1="H:\CERN\Timesheets\2009-2010\"

and then tried the formula with

=SUM($A$1[Adrian Nolan 2009-2010.xls]Jul'!$H$46

But not working! What am I missing?

 

[Kaps_mr2]

you need :-

INDIRECT(SUM($A$1&[Adrian Nolan 2009-2010.xls]Jul'!$H$46)

and the other workbook needs to be open. Thanks

 

[Andrew Poulsom]

Another possibilty is to create named ranges that refer to the external workbooks. Examples:

Name: AdrianNolan
Refers to: 'H:\CERN\Timesheets\2009-2010\[Adrian Nolan 2009-2010.xls]Jul'!$H$46

Name: FrancisMarkus
Refers to: 'H:\CERN\Timesheets\2009-2010\[Francis Markus 2009-2010.xls]Jul'!$H$46

Then you can use a formula like:

=SUM(AdrianNolan,FrancisMarkus)