Forum Discussion
Solved
Need a formula that advances sheet name by one month
I have a formula in a cell, ='Apr 2019'!M13 This is for May 2019. When I create June 2019 sheet I need the Apr part of the formula to change to May 2019. Also same thing with this cell formula, =IFER...
Hi,
You may add into each your sheet parameter cell with the name of previous month. Let say it's D1, when in June table in D1 it will be May and straightforward formula is
=IFERROR((Q81/INDIRECT("'" & $D$1 & " 2019'!Q81"))-1,0)However, it doesn't change the address of the cell Q81 when you copy the cell with formula, thus it could be bit expanded as
=IFERROR((Q81/INDIRECT("'" & $D$1 & " 2019'!" & CELL("address",Q81)))-1,0)And 2019 is also better to take as parameter.
Hi,
You may add into each your sheet parameter cell with the name of previous month. Let say it's D1, when in June table in D1 it will be May and straightforward formula is
=IFERROR((Q81/INDIRECT("'" & $D$1 & " 2019'!Q81"))-1,0)
However, it doesn't change the address of the cell Q81 when you copy the cell with formula, thus it could be bit expanded as
=IFERROR((Q81/INDIRECT("'" & $D$1 & " 2019'!" & CELL("address",Q81)))-1,0)
And 2019 is also better to take as parameter.
I guess I should have added the worksheet name. Each sheet is named Jan 2019, Feb 2019, Mar 2019, etc.
First 3 letters of the month space then current year 2019
But I appreciate the help, I think I know where you're going but the worksheet name with the space in it might need extra work.
I did up to Jun 2019 so the way I do it is copy all the cells in Jun 2019 and paste and into new sheet and manually change all the references to May that June used into the July 2019 worksheet with references to Jun 2019.
=IFERROR((C14/'May 2019'!C14)-1,0) This is the Jun 2019 worksheet, so July worksheet would need to change May 2019 to Jun 2019.
then another formula, =SUM('Jun 2019'!C14-'May 2019'!C14), in July worksheet I would change the months,
=SUM('Jul 2019'!C14-'Jun 2019'!C14)
Thank You for any help clearing this up
Yes, that was only an idea, which parameters to use that's up to you - months only or entire sheet name. And it looks like you need another parameter cell with the name of the current sheet. With this, if you copy your sheet on next month the only you need is to change the values of these two cells.