Excel 2016 Automate some worksheets [Solved/Closed]

Report
Posts
2
Registration date
Saturday December 26, 2015
Status
Member
Last seen
December 27, 2015
-
Posts
2
Registration date
Saturday December 26, 2015
Status
Member
Last seen
December 27, 2015
-
I have two questions. I want to Automate some worksheets.

1. If I enter say "March" in cell A1 in Sheet 1, I want it to automatically put April in Cell A1 on Sheet 2 etc. It must be dynamic so that if sheet 1 A1 is changed then all subsequent sheets also change.

2. As the data in Cell A1 changes on a sheet, I want the Tab name to change to that in Cell A1.

Can anyone help me out

1 reply

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
135
Hi ForeverLearning

First of all, I'm not an Excel expert, but am learning fast.

Unfortunately Excel doesnt provide the renaming of worksheets natively. However, it can be done with a macro, but this would require you to run the macro every time the cell A1 is changed. The following website discusses this:

https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html

As for naming the A1 cell in every sheet, try this, but unfortunately it meeds a full date to be inputted somewhere:

In Sheet 1 Cell A1 type
=CHOOSE(MONTH(B1),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC")

In Sheet 2 Cell A1 type
=CHOOSE(MONTH(Sheet1!B1)+1,"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC")

In Sheet 3 Cell A1 type
=CHOOSE(MONTH(Sheet1!B1)+2,"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC")

Keep going until you have the required number of sheets.
In Sheet 1 Cell B1 type a date with the month you need in Sheet1 Cell A1

Hope this works for you ... at least till you find a better way.

I really appreciate thank you messages as a payment for solving issues   :o)
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2
Registration date
Saturday December 26, 2015
Status
Member
Last seen
December 27, 2015

Hey Brian, Thanks for your help on this, it is greatly appreciated. Wishing you a Safe, Happy & Prosperous New Year to you and yours.
Cheers
Mark
(ForeverLearning)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!