Auto populate when copying new worksheet

[something68]

I have a year, say 2011, in cell M1 in first worksheet. I would like to copy sheet 2011 and have the M1 cell auto change to the new year, 2012.

Thank you.

 

[Joe4]

If 2011 is on Sheet1, and you are inserting a new sheet and was 2012 on Sheet2, replace your hard-code value of "2011" in cell M1 on Sheet1 with this formula:

=2010 + SHEET()

and that formula should show 2012 on your new sheet (and 2013 if you add another one after that, etc).

 

[something68]

Thank you. I will give that a try.

 

[something68]

If 2011 is on Sheet1, and you are inserting a new sheet and was 2012 on Sheet2, replace your hard-code value of "2011" in cell M1 on Sheet1 with this formula:

=2010 + SHEET()

and that formula should show 2012 on your new sheet (and 2013 if you add another one after that, etc).

In sheet 2, I used =2021+sheet(2021) and that worked, but it looks as though I would have to go to each sheet and manually change the =YYYY so I was wondering if there was a way to automatically change the =YYYY(Sheet(). See screenshot below - as you can see there are many sheets and after 2028 (when I retire ) I would like the next person (if they want to continue using these sheets) to simply copy 2028 to a new sheet - 2029 - and not have to do too much work on updating, etc.

Thank you!

 

[Joe4]

Why did you change my formula? By putting something in between the parentheses, you remove the dynamic nature of it.

Note that when you use:

Sheet()

you return the sheet number of the current sheet (that the formula is found on).

So, if you were on your first sheet, that formula would return 1.
On the second sheet, that formula returns 2.

So, then if you use the formula I gave you and put it in cell M1:

=2010 + SHEET()

it will return 2011 on the first sheet (2010 + 1),
and then 2012 on the second sheet (2010 +2),
and then 2013 on the third sheet (2010 + 3).

So once you set up the formula in this manner to return the correct value on the original sheet, it will automatically return what you need on any new sheets you add and copy over.
The only thing you should ever need to change is the starting value on the original sheet (2010), and nowhere else.
The number in the original formula should be one less than the first year you want (so that when you add one to it, it returns the proper value).

In the example you shown above, your formula in M1 should just be:

=2020 + Sheet()

 

[something68]

Why did you change my formula? By putting something in between the parentheses, you remove the dynamic nature of it.

Note that when you use:

Sheet()

you return the sheet number of the current sheet (that the formula is found on).

So, if you were on your first sheet, that formula would return 1.
On the second sheet, that formula returns 2.

So, then if you use the formula I gave you and put it in cell M1:

=2010 + SHEET()

it will return 2011 on the first sheet (2010 + 1),
and then 2012 on the second sheet (2010 +2),
and then 2013 on the third sheet (2010 + 3).

So once you set up the formula in this manner to return the correct value on the original sheet, it will automatically return what you need on any new sheets you add and copy over.
The only thing you should ever need to change is the starting value on the original sheet (2010), and nowhere else.
The number in the original formula should be one less than the first year you want (so that when you add one to it, it returns the proper value).

In the example you shown above, your formula in M1 should just be:

=2020 + Sheet()

Thank you. My apologies for changing your formula, I may have been trying to make it work for what I was doing but it's working and I will make note of your formula.

Thanks again!

 

[Joe4]

You are welcome.
Glad I was able to help!