Report

Excel 2016 Automate some worksheets [Solved]

Ask a question ForeverLearning 2Posts Saturday December 26, 2015Registration date December 27, 2015 Last seen - Latest answer on Dec 27, 2015 12:27AM
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
See more 
Helpful
+2
moins plus
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:

http://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.

ForeverLearning 2Posts Saturday December 26, 2015Registration date December 27, 2015 Last seen - Dec 27, 2015 12:27AM
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)
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!