path title in formulas

[TedSki]

I have a question that I am not sure there is an answer to but here goes. I have two workbooks 1 is an admin workbook and the other is a staff workbook. The staff workbook is say "Month One Work.xls". The admin workbook has formulas in each cell that retrieves a specific total from the staff worbook (=["Month One Work]!A114). Both are saved. When the staff creates "Month Two Work.xls", I need to open a new admin workbook with the formulas accessing the "Month Two Work.xls" workbook. Is there any way to do this without having to change every single formula? There are over two hundred formulas. I was wondering if there was way to put the file name in cell A1 ("Month Two Work.xls") and have the formulas pick up the title in each.
I would appeciate any way this could be done without individually changing each cell manually.

 

[Tom Urtis]

If you are saying that Month Two Workbook inormation will only be represented, and not Month One Workbook anymore after the Month Two Workbook is gerenrated, then in your admin workbook you can simply select the sheet with the formulas, pres Ctrl+A+A, then hit Ctrl+H and in the Find What field enter
Month One Work]
and in the Replace with field enter
Month Two Work]

Select for Look in Formulas, deselect Match entire cell contents, click Replace All and that should do it.

Keeps things manual which sounds like you will be doing 12 times a year, each time takes a couple seconds so no need for a macro.

 

[TedSki]

Actually MONTH ONE workbook will be saved, the MONTH TWO, then MONTH THREE, and so on so a history folder will contain the whole year's worth of sheets.

 

[Tom Urtis]

You're not making sense.

You wrote:
"so a history folder will contain the whole year's worth of sheets."

which means either
so a history folder will contain the whole year's worth of workbooks.

or

so a history workbook will contain the whole year's worth of sheets.



Either way, just copy the cells from the first Month One worksheet to 12 more worksheets (whether they are each in 12 separate workbooks or 12 sheets in one workbook) and spend a minute to do 11 (total of 12 but you already have the first one done) Find and Replaces for Month Two, Month Three, etc.