I have an annual income statement that I would like to copy data to a new file every year. Every year the new file name is for the previous year(2015).
This file was built in 2015 with the data coming from the 2014 file name.
2014 2013

$58,379 $55,130
11 23
58,390 55,153

12,601 12,525
1,676 3,719
For the new file(2015) I want to copy data from column 1(2014) to column 2(2013) in the new file. The problem I have is every year the file name will change by one number. This formula works for this year but won't copy right for the next year, =[2014.xlsx]Income!$E11
Is there some way to get the file name from the date at the top of my column? This way I would just have to change the date in one spot.

Although an INDIRECT function could be used to generate the required reference, that function won't work with a reference to a closed workbook. The best solution for you would probably be to use Find/Replace to update the references each year. Alternatively, you might consider having all years in the same workbook. Although you could have each year in a separate worksheet on that workbook, the best long-term approach for data aggregation and analysis is to have all the data on a single worksheet. Formulae can be used to generate queries for any nominated period from a multi-year worksheet.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

There are 7 sheets in each workbook. That is why I save it in a new file every year.

The number of analysis sheets is of no consequence; it's how they're configured with formulae, etc. to reference the data sheet that matters. Correctly done, you might at most need a single cell somewhere to set the year value (or two cells to set a date range) for all the analysis sheets.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I appreciate your responses macropod but they don't give me any kind of solution.

I have given you two:
• An annual Find & Replace; or
• Consolidate the data into a single workbook.
The latter is the better long-term solution.

You're free to ignore the advice, but kindly don't say the answers "don't give me any kind of solution" just because what you're offered isn't exactly what you want. Wanting something doesn't automatically mean it can be done in the way you want it.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]