Excel:Using Functions that span multiple workbooks

[Terpsfan]

I am able to create functions that span multiple worksheets but seem to be unable to do this for multiple workbooks. I am sure there is a way to do it but can't find it handy. Thanks in advance!

 

[Dreamboat]

We'll need a little more information than that.

What kind of function?
How many worksheets?

Anne Troy
Supercharge your Office Applications:

http://www.vbaexpress.com

 

[SkipVought]

Hi,

The Workbook containing the macro MUST BE OPEN for the macro to work in OTHER workbooks.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Terpsfan]

I'm trying to derive a correlation coefficient based on values in different workbooks- 6 in all. Each workbook represents a report from a certain month. I have workbooks named "October 2003.xls"..."November 2003.xls". I wanted to derive the correlation between Overall Loan Compliance and Credit Compliance, etc for the 6-month period. Overall Loan Compliance and Credit Compliance are cells in the same location on all workbooks.

 

[KenWright]

You could possibly save yourself a lot of hassle by not keeping all this data in different workbooks. Whislt it isn't always feasible to store everything on a single sheet, it can often make things a lot easier to track. Even if a single sheet isn't viable, then multiple sheets with an identical structure will still be preferable to multiple workbooks, and will allow you to drill through the sheets quite easily compared to what you are doing now.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Dreamboat]

correlation coefficient

He's talking dirty.
I'm outta here.

Anne Troy
Supercharge your Office Applications:

http://www.vbaexpress.com

 

[KenWright]

LOL - Talk about the Pot calling the Kettle black

Maybe I should link back to a number of Anne's more recent threads

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Ken, you stole my thunder!

I'm supposed to be the BAD COP when it comes to data normalization or lack thereof.

LOL yer gonna get a BAD reputation!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Dreamboat]

Ken, after Skip's scathing remarks:

Anne Troy
Supercharge your Office Applications:

http://www.vbaexpress.com

 

[KenWright]

rotflmao:-

Skip - Couldn't resist it

Anne - I swear you sit around all day creating smiley's

Anyway, back to the OP, Why not just build yourself a summary sheet with full filepath links to the cells you want to pull in? Then do your analysis on that data.

Regards
Ken.....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Terpsfan]

Yes, I'm aware that storing data in one workbook would be the better solution. In this case it's not viable because each month has 18 tabs worth of spreadsheets.

What I'm going to do is take all the data that is now in spreadsheets and move them over to Crystal Reports then export to an Adobe document.

 

[Fherrera]

Omega36:
I am able to create functions that span multiple worksheets but seem to be unable to do this for multiple workbooks. I am sure there is a way to do it but can't find it handy. Thanks in advance!

I'm not sure how to do it for multiple worksheets? Like say if i wanted to SUM A1:A5 in Sheet1 as well as SUM A1:A5 in Sheet2... (this is a simple example the formula I will need to use is more complicated).. I'd like to do this all with just one function call? Possibly using the Sum keyword only once? Is this possible?

Thanks

Frank

 

[KenWright]

=SUM('Sheet First:Sheet Last'!A1:A5)

By the way, does all the data in each tab have the same structure, albeit it may have differing amounts of rows, and also how many rows on average does each sheet have.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------