Dynamic YTD Function

[miked123]

I have one row of month-years (mmm-yy) in a file from 2014-2019 across 72 columns. I would like to calculate the YTD number of a given month-year for many line-items of an income statement going down each row below the dates. I have had luck with formulas like sum and index however when I close the file with months my calculation in my other file is giving me a reference error.

In addition to this, I would like to go down each row of line items to calculate the YTD number; I have index references for the appropriate row number.

An example would be if I choose to view Sept-2016 YTD of Revenue. I would like the formula to go into the destination file - pull the sum of numbers from Jan-16 through Sept-16. Similarly, I would like this formula to work for expenses, income, etc.

Has anyone ran into this before? If so, could you please help on brainstorming a formula?

I have heard that sum and sumif formulas ref out when the destination file is closed - apparently sumproducts are able to work despite the file being closed.

Thanks,
Mike

[mikeTRON]

Why bother with this issue of having the data in another workbook, why not just pull it into the workbook doing the reporting? Then your YTD formula issues is a non issue.

[miked123]

Hi Mike,

I am pulling 7 business unit financial models into one workbook designated as a high level reporting tool (mentioned above file). These models are quite large with about 800 line items all being fed through Cognos and they have forecasted calculations.

Any suggestions??

[mikeTRON]

In the past I used simple VBA to pull the dataset in to ONE tab, that then allowed for absurdly simple calculations and reporting, but I don't know how confident you are in VBA. You don't want a solution you dont understand and can't audit and/or modify.

[miked123]

I am getting better with VBA - I have created some macros. I will have to start practicing more. I hardcoded the models in my file so I can do the calculation btw - will take up some space but whatever!

[mikeTRON]

Yeah, even a simple copy and paste to avoid the issues with data connections is typically my preferred method. More than the formula issues, I can't stand having to audit LINKED files.... it drives me insane.