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)

 

[Domski]

I have a spreadsheet which uses 16 named ranges to identify 16 individual workbooks, however every time I copy/change a formula that uses them, I have to re confirm the file location! Any idea why, given the named range contains the file location!

I have put together the formula below:

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

Then when I copy the formula to the next cell and use find/replace to change the formula to Aug'!$H$46 it forces me to locate every individual file on my server.

My named ranges are set up like this:

="H:\CERN\Timesheets\2009-2010\[Adrian Nolan 2009-2010.xls]"

From here: http://www.mrexcel.com/forum/showthread.php?t=392412

If you're going to hold just the file name and not a reference to a range you will need to hold it like:

="'H:\CERN\Timesheets\2009-2010\[Adrian Nolan 2009-2010.xls]"

(added a ' at the start)

And then use Indirect like this:

=INDIRECT(Adrian&"Sheet1'!$H$46")

Dom

 

[stuartmacdonald]

Does INDIRECT not require the workbook to be open?

 

[RoryA]

Yes, it does. I would suggest that you add a new workbook, use straight cell links to the cells in the closed workbooks you need, then sum those cells in your workbook. There might be better ways (other than rearranging your data) but we'd need to know more about the layouts to be sure.

 

[snowblizz]

It more or less looks like a VBA macro/code would be the way forward here?

 

[Domski]

Does INDIRECT not require the workbook to be open?

Yes, but you can't construct a working reference to a range from multiple strings without it.

It more or less looks like a VBA macro/code would be the way forward here?

I wouldn't rush into using VBA unless you really need to. As Rory suggests there are workarounds that you can use using native Excel functions that would probably work better.

Dom

 

[stuartmacdonald]

How do I post the workbook? Is that a done thing?

 

[RoryA]

You can use a file hosting site like box.net (or any of the other free ones) and post a link back here. Ideally we would need to see your summary workbook and one source workbook (assuming they all have the same layout?) and a description of what data you want retrieved and to where.

 

[stuartmacdonald]

I attach 2 files - the first is a timesheet manager that sums the timesheets of all our staff, the second is an example of a timesheet - they are all exactly the same. You can see in the first sheet ('Overview') of the timesheet manager what Im trying to do.


Timesheet Manager

http://rapidshare.com/files/23741724/Timesheet_Manager_2009-10.xls.html

Example Timesheet

http://rapidshare.com/files/237416853/Adrian_Nolan_2009-2010.xls.html

 

[RoryA]

The first of those links seems to be broken?

 

[stuartmacdonald]

Timesheet Manager

http://rapidshare.com/files/237423922/Timesheet_Manager_2009-10.xls.html