Formulas and Multiple worksheets

dah2

New Member
Joined
Sep 13, 2006
Messages
1
In our office we are working on a project where we want to track certain items weekly for approximately 40 weeks, but maintain individual weekly sheets. We also would like to have each week's sheet use data from the sheet of the preceding week. We have had no problem creating the individual worksheet with the necessary formulas and creating additional weekly sheets within the same file. Our problem comes when we attempt to copy formulas from sheet to sheet in a relative format, particularly with the formula we desire to refer to the previous sheet. Any suggetions would be appreciated.
 

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.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Welcome to the board!

One method to do this is to use the Indirect() function in each formula where you need to refer to a relative sheet name. Example: =indirect(a1&"!B1") will refer to cell B1 on whichever sheet name appears in cell A1 of the current sheet. You can write all of your equation in this way, copy them to a new sheet, then change the text in cell A1 and all of your formulas will refer to the new sheet.

One downfall is that if anyone changes the name of the sheet, it will generate an error. To get around this, you can use this formula in cell A1: =RIGHT(CELL("filename",Sheet1!IV1),LEN(CELL("filename",Sheet1!IV1))-FIND("]",CELL("filename",Sheet1!IV1))), and simply change the references as required... then when someone change sthe sheet name, it will update in cell A1.
 

Forum statistics

Threads
1,136,350
Messages
5,675,246
Members
419,557
Latest member
razlevav

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