[excelanswers]

I have a "rollup" workbook that I need to populate with data from other workbooks. The other workbooks all have the same names with the exception of their ending which is the name of each month of the year. I want to have only
ONE "rollup" workbook that I will have the ability to change the name of the workbook that I need to pull data from by simply changing one cell in the "rollup" workbook. For example if the source workbook is called sourcebookJanuary2012.xlsx and another is called sourcebookFebruary2012.xlsx
I want to simply change a cell in the "rollup" sheet to either January2012 or February2012 to pull data from the respective sheet without having to use the Find and Replace function. The problem is getting part of the reference formula to allow substitution of part of it with the proper month of say January2012, February2012, etc.

[JBeaucaire]

I'm having trouble being certain you mean separate workbooks, or separate worksheets within a single workbook.

If it's SHEETs within a workbook, you could use the INDIRECT() function to piece together a reference string that resolves to a specific sheet and range. For instance, you could put "Sheet1" in cell A1, then in B1 this formula would go get the value from B10 from that sheet:

=INDIRECT("'" & A1 & "'!B10")

INDIRECT() is perfect for this and can even be used to create references to other WORKBOOKS, but only if those workbooks are open. You cannot INDIRECTly reference a closed workbook.

If you MUST indirectly reference closed workbooks, then you'll have to install more functionality into your Excel program. (Only people with the same installed ADD-IN will be able to use your sheet.)

One such free ADD-IN is called MoreFunc... read all about it here:
http://xcell05.free.fr/morefunc/english/

Download and install it from here:
Morefunc - CNET Download.com

Go into TOOLS > ADDINS and activate MoreFunc.

Now you have many, many new functions available to you. Any place you used INDIRECT, now use INDIRECT.EXT instead and it will work on closed workbooks.

[Chic_Bowdrie]

Do your source workbooks contain more than one sheet? If not, you can use "Import External Data" under the data tab to do this. Select your target source file and sheet. Excel prompts you to put the data somewhere. The default is A1 and if you put it anywhere else, the query will go there too. To change from Jan to Feb, right click the upper left cell (or wherever the query is) and click "Edit query ...." In the Connection field, look for "DataSource=" and edit the file name that follows. If the sheet name isn't the same on all workbooks, you may have to change the sheet name too.

There is another way to reproduce sheets from other workbooks by putting the upper left cell from the source sheet in the rollup worksheet. Then copy the cell and paste to the other cells in the sheet. You can change the source name in any cell, copy it, and paste it to the whole sheet to change all the cells.

If your sourcebooks have more than one sheet, you would have to edit the data source for every sheet in the rollup.