Formula too long! Summing 16 workbooks

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)
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,534


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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jul 5, 2008
Messages
1,534

ADVERTISEMENT

Thanks Chaps forgot about the INDIRECT part !!!

Kaps
 

stuartmacdonald

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

Well-known Member
Joined
Jul 5, 2008
Messages
1,534

ADVERTISEMENT

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

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

Well-known Member
Joined
Jul 5, 2008
Messages
1,534
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top