Dynamic reference to a worksheet on a different workbook

I have two workbooks called Source and Destination. Each have 12 tabs names January, February, etc

I need a formula that will dynamically take the name of the worksheet the formula is on on the Destination Workbook and obtain data from the SAME named worksheet on the source workbook. I want to create all the formulas on the January worksheet of the Destination Workbook that will grab the data from the January worksheet of the Source workbook but when I copy that formula to the February worksheet, it will know to grab that data from the February tab of the Source worksheet. In other words, I don't want to type January in the formula but want it to KNOW to go to the worksheet name of the source workbook based on whatever the Destination tab name the formula is on. I assume it has to use the indirect function. I know the formula to get the Sheet name but am having trouble with the syntax to use in the formula to extract the data from the other workbook.

Thank you for this. It is closer to what I want but has a problem.

One Clarification, your comment suggests you thought I wanted data on the January tab to change which tab from the Source data comes from when I "copy down". That is not what is intended. EVERYTHING on the Destination January tab should return data from the Source January tab. The goal is to be able to create all the formulas on the January tab and then be able to simply copy all the formulas to the February tab and the February tab will automatically get data from the Source February tab.

1. Instead of putting all the months in column C, I put in a formula in A1 (=TRIM(MID(SUBSTITUTE(CELL("filename",A1),"]",REPT(" ",99)),2*99-98,99)) that returns the tab name and then used your formula but changed it to =INDIRECT("[Source.xlsx]"&A1&"!$A$3"). That worked for Cell A3 getting the data from the correct tab in the Source File. I then copy that same formula to the February tab and it again gets the correct data from the February tab of the Source Tab. So far so good.


2. However, when I copy the formula in Destination/January Tab/C3 to January C4, it does not return the data from the Source file/Januar yTab/C4, it returns the data from Source file/January Tab C3. I assume that is because it is using indirect. Is there a way to copy down from the C3 formula and have it return the correct data from the source file. In my simple example, when I copy the formula in Destination January C3 (which correctly returns the data from Source/January C3) to Destination January C4, I want it to return the data from Source/January C4. Likewise, when I copy all the formulas from the January Tab on the February Tab, It should return the data but from the Source February tab.

Your solution does the second part but not the first because C3 does not change when I copy down or across.

I can't seem to upload a file or even a direct link here so here is the path dropbox.com/s/sla5gcpe5c1dmiw/Destination.xlsx?dl=0[/url] to the new file with the new formulas. This works for cell A3 only but when I "copy down or across" It does not change the source cell so all are the same.

I hope that is clear and I thank you for any additional help you can provide.