I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month.
Each sheet references cells in the previous sheet. For example, the "May" sheet is drawing data from the "April" sheet, the "June" sheet will draw data from the "May" sheet, etc.
Let's say I create the first and the second sheets, "April" and "May". If I copy "May" and I rename it "June", all the formulas will still refer to "April" as in the "May" sheet. I would like that they refer automatically to "May" instead.
How is it possible to do this?
Best Answer
Create the following macro:
Then, if you want the May sheet to access the
April!Q42
cell, use the formulaThe first argument is the sheet number, relative to the current one, and the second argument is the cell reference. When you copy this formula to the June sheet, it will automatically reference the May sheet.
Source: I found this at The Spreadsheet Page.