Data from multiple Workbooks, certian years only (repost)

Walking Shorts

New Member
Joined
Nov 23, 2005
Messages
17
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
anthonya2369 said:
You mean xls instead of xml?

:confused: 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 :p
 
Upvote 0

Forum statistics

Threads
1,211,744
Messages
6,103,678
Members
447,876
Latest member
rodqntr

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
Back
Top