Linking two workbooks and corresponding worksheets

[sogs0707]

So I have two workbooks, one contains raw data, one contains summary tables.
Each workbook has a worksheet for each month. The summary tables contain formulae to draw through the raw data into the right summary tables.

So if I have created everything so that the January summary table looks to the January raw data. Each time I create a new worksheet for a new month how can I tell it to look at the right month without having to go through and manually updating my formulae to look at the right worksheet?

I hope thats clear and that someone can help?!

Sogs0707

 

[ParamRay]

.
.

When you add a sheet for the new month, you could simply do a Find and Replace All in order to replace the month in all your formulas to the new month...

 

[sogs0707]

.
.

When you add a sheet for the new month, you could simply do a Find and Replace All in order to replace the month in all your formulas to the new month...

Thanks, that was my solution so far. It's fine for me but I"m worried one day someone else might need to use the spreadsheet and needs things super ABC...

I think the easiest thing for now might be just for me to add a sheet for every month for the whole year.

 

[BlueHornet]

My own suggestion is to learn to use the INDIRECT() function.

For example, if your summary worksheet for January has that name in a cell on the worksheet (let's say A1), and you want to capture data from the "January" raw data worksheet, then you could use something like =indirect( "'" & $A$1 & "'!( [Cell Reference here on the 'January' Raw Data sheet] & "'")

Note the inclusion of " ' " (entered there with spaces that you must not use in your formula, since the extra spaces around the single quote character would ruin the INDIRECT text) at the beginning and end of the formula.