Year-to-Date Summation/Vlookup Formula

[thom0900]

I have a spreadsheet with a few tabs, one of which summarizes a few other detailed worksheets with financial data by month. I need to create a formula that dynamically adds year-to-date information from another worksheet. The formula I thought would work was: sum(OFFSET(VLOOKUP($A13, Data, MATCH('File Info'!$B$2, 'Format-Data'!$1:$1,0),0),0,0, 1, month('File Info'!$B$1))). Data = detailed data worksheet (named range), Format-Data is the same worksheet, File Info tab has first month of current year and end of current month dates. This throws up an error.
Apparently VLookup and Sum/Offset don't work well together - any ideas?
<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=99><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 2816" width=99><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; WIDTH: 74pt; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=21 width=99></TD></TR></TBODY></TABLE>

 

[Robert Mika]

Could you post a sample of your data?

You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[thom0900]

Thanks for the reply, but I cannot download these programs from work. However, maybe a further explanation would help: My summary worksheet has a year-to-date column that links to a sparate data worksheet in the spreadsheet with many columns of monthly data dating back a couple of years. My thought was to create a dynamic formula that would lookup the correct row and column of data and then sum the year-to-date rows based on the current input date (which is on a separate tab, along with the base date 1st month of year). My month to date formula (1 column) works, which is: =VLOOKUP($A18,Data,MATCH('File Info'!$B$1, 'Format - Data'!$1:$1, 0),0); however, when I tried to make it into a dynamic formula for YTD information (previous formula noted in the original post), it does not work. Any thoughts on how I can get this to work?