Data from multiple Workbooks, certian years only (repost)

[Walking Shorts]

Hey dudes, I guess this post must have been deleted or something because it was a bit of a repost of a question I had formerly asked. Sorry about that, I was just trying to clean up my question so it wasn't divided into multiple posts and easier to understand. If something must be deleted, please delete the other post, as it's probably quite confusing to read compared to this one

Oy, my brain hurts, hopefully y'all can give me a hand.

I want to make a worksheet that will display totals of data collected from various other workbooks for a certian year. Each workbook file has been named according to which month and year the data is from, such as dec03.xml, or feb04.xml and so forth. For simplicity sake, I'll say that the data I want from each file is contained in cell B4 on a sheet titled "monthly".

So, what I want to to is set the worksheet up so that when you type a year in, say 2002, the total data in cell b4 on the monthly sheet will appear from the workbooks from 2002 only (jan02.xml, feb02.xml, etc.). Any suggestions?

Here's what I've managed to come up with, but it's not working too well yet, hopefully someone can help me out. OK, so cell D3 has "2006" in it right now, and the filenames I'm using are names such as "dejan06.xls, defeb06.xls, demar06.xls, etc.". I've managed to get this so far-

=IF(INDIRECT("[dejan"&(RIGHT(D3,2))&".xls]Monthly!D6")="",0,INDIRECT("[dejan"&(RIGHT(D3,2)&".xls]Monthly!D6")))

So I was thinking of just repeating that formula to cover every month of the year. I think the part that is really giving me trouble is the fact that my formula needs to support a "Year to date" format, so if I need the totals from a certian year before the year is done, I can still get the totals thus far in that year. If you've got any ideas please post, It'd be greatly appreciated!

 

[anthonya2369]

You mean xls instead of xml?

I would Set it up like this:

Year 2006

Jan 06 * =text(a2,"yy")
Feb 06
Mar 06
.
.
Nov 06
Dec 06
Year 06 * =sum(b2:b14)

Hide the Jan and 06 if you want and then just reference each column for Month and Year and it automatically updates when they change the year at the top.

Just a try

 

[Walking Shorts]

You mean xls instead of xml?

Yes, yes, sorry about that, don't know why .xml popped into my head instead of .xls

But yeah, thanks for the suggestion, I'll have to try it out, sounds a lot simpler than what I've been trying