Complicated Excel Macro

[Charlie Buchanan]

Hi All,

Myself and some of my colleagues have been toying with Excel macro's however are not too experienced with it, there was a specific macro we were wondering if it is possible to create and if so how, hopefully someone can help

Equivalently, we have 12 sheets on an excel save, and we need to allocate these sheets unique months depending on who uses the spreadsheet.

What we want to achieve is a macro on the first page where you type in the first month (any month, depending on the user) and the following 11 pages will update and amend to the following months.

For Example, if we are to put March in the first sheet, the following sheets will display April, May, June, July etc,... all the way back around to February.

However if we were to put in July, the follwing sheets will display : August, September, October etc up to the 11th sheet which would display June.

Quite a complicated specific request, however, if anyone knows of a macro or any way to do this on Excel, it would be greatly appreciated.

Kind regards,

Charlie

 

[Craggs82]

Hi Charlie, do you need the sheet name to change, or is it a cell value inside the sheet? the former will need some VBA, the latter could be a formula.

 

[Charlie Buchanan]

Hi Charlie, do you need the sheet name to change, or is it a cell value inside the sheet? the former will need some VBA, the latter could be a formula.

The Sheet names can remain "Month 1, Month 2..." etc, would be nice to be able to amend these to change, however, is not a necessity.

The use of the spreadsheet will be for tracking yearly bank statement expenses, however bank statements starting from different periods for different people.

It would be a handful of cells (no more than 40) per sheet that will need to auto-amend to the appropriate dates.

Ideally, we input for example "January" on the first sheet, the following sheets adjust to the appropriate following months in order to make a full year, and within the sheets will be the dates of these expenses.

The following expenses will then be manually input and categorised to the appropriate boxes that we have already created on our spreadsheets

Thank you for the prompt response also

 

[Craggs82]

Ok, so i'd start with just trying formula. Its a more comfortable place for the vast majority of Excel users and, although the overheads and calculation times may be a little higher, it should be ok.

Its a nice simple start, on each sheet have a formula in a cell which either -

• references the cell in which you typed your initial date, and adds a set amount of months to that date.
• Have each sheet reference the previous sheet and add a single month.

lets say we've got 01/01/2022 in cell A1 on the first sheet. you can format this as 'mmmm yy' (January 22) in custom formats if that's the desired result.

On sheet 2, you'll have a formula which references that date and adds a month. for ease lets say that's also cell A1.

=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,DAY(Sheet1!A1))

you can then either, change the +1 value to the months on each sheet to +2, +3 etc. Or you could change the sheet name in the formula to reference the previous sheet (Sheet2!, Sheet3!)

once you've got this in place you could then reference that cell on each sheet to populate your specific dates.

Its not much, and it seems like quite a hefty project, but hopefully that should help you on your way.

 

[Charlie Buchanan]

Thats brilliant, thank you.

Is there a way to do this in the format "Month Year" ? So it will just display "March 2022" for example?

Thank you again,

Kind regards,

Charlie

 

[Charlie Buchanan]

Ok, so i'd start with just trying formula. Its a more comfortable place for the vast majority of Excel users and, although the overheads and calculation times may be a little higher, it should be ok.

Its a nice simple start, on each sheet have a formula in a cell which either -

• references the cell in which you typed your initial date, and adds a set amount of months to that date.
• Have each sheet reference the previous sheet and add a single month.

lets say we've got 01/01/2022 in cell A1 on the first sheet. you can format this as 'mmmm yy' (January 22) in custom formats if that's the desired result.

On sheet 2, you'll have a formula which references that date and adds a month. for ease lets say that's also cell A1.

=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1)+1,DAY(Sheet1!A1))

you can then either, change the +1 value to the months on each sheet to +2, +3 etc. Or you could change the sheet name in the formula to reference the previous sheet (Sheet2!, Sheet3!)

once you've got this in place you could then reference that cell on each sheet to populate your specific dates.

Its not much, and it seems like quite a hefty project, but hopefully that should help you on your way.

I have been toying with this formula for a bit trying to wrap it around our system, but to no luck.

To break down to basics, we have a "Set up" sheet, to which people will insert "Month" into B3 and "Year" into B4, this is to be used with a Drop-Down menu where they can select their month and year.

For example in the month box you will select "September" and in the year box "2022", preferably we would like to keep the inputting of data like this and in this format.

Our second sheet is titled "Month 1" and in cell A3 we have a formula ='Set up'!B3&" "&'Set up'!B4 to make the first month exact to what the user input in the "Set Up" sheet.

This displays the date as "September 2022" in cell A3 on the sheet Month 1.

Our Third sheet is titled "Month 2" and what we are having troubles trying to figure out is how to make this sheet display +1 month from "Month 1".

In this instance we want sheet "Month 2" to display October 2022 in this format and the following sheets to increase by 1 month.

Kind regards,

Charlie

 

[Craggs82]

The problem you have by concatenating the 2 values with an '&' is that excel will interpret this as a string rather than a date, so manipulation will be limited.

Ideally you need excel to see the value as a date, but display in your required formatting. We can either format the cell using Custom formatting options to "mmmm yyyy" and use

=DATEVALUE("01/"&'Set up'!B3&"/"&'Set up'!B4)

or we could force the date to text and apply formatting in the formula like so -

=TEXT(DATEVALUE("01/"&'Set up'!B3&"/"&'Set up'!B4),"mmmm yyyy")

Either of the above would be applied to your 'Month 1' sheet in cell A3. A point to note here is that excel requires a day, so i've defaulted this to the first of the month (I'm assuming UK date formats here, swap them round for US).

From there you should be able to manipulate the value as a date inside Excel using the earlier function to add a month, for month 2 the formula would look like -

=DATE(YEAR('Month 1'!A3),MONTH('Month 1'!A3)+1,DAY('Month 1'!A3))

On the month 3 sheet and so on, you can simply change the +1 to a +2. and so on for the rest of the sheets. For reference, month 3 would look like -

=DATE(YEAR('Month 1'!A3),MONTH('Month 1'!A3)+2,DAY('Month 1'!A3))

if you are using the in cell text formatting, you'd need to wrap the formulas in the TEXT function. Month 2 for example.

=TEXT(DATE(YEAR('Month 1'!A3),MONTH('Month 1'!A3)+1,DAY('Month 1'!A3)), "mmmm yyyy")

Hope that helps

 

[Charlie Buchanan]

That's perfect! exactly what we wanted, thank you so much.