Linking two workbooks and corresponding worksheets

sogs0707

New Member
Joined
Sep 25, 2014
Messages
13
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

New Member
Joined
Sep 25, 2014
Messages
13
.
.

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

Active Member
Joined
Apr 13, 2012
Messages
338
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,336
Messages
5,624,093
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top