# Data from multiple Workbooks, certian years only (repost)

#### Walking Shorts

##### New Member
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

anthonya2369 said:
You mean xls instead of xml?

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

Replies
2
Views
404
Replies
6
Views
601
Replies
19
Views
1K
Replies
0
Views
280
Replies
11
Views
3K

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.

### Which adblocker are you using?

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

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