Macro Question

[Weasel]

I've got a sheet that has a daily, weekly, monthly, and yearly total. All of those #'s are pulled from other sheets in the workbook using formula's. One example of a formula is:

=VLOOKUP($A$1,actuals!$W$4:$Z$43,2)

When a new month starts I have to change part of the formula's. For this formula I'd have to change W to AD and Z to AG. If I wanted to go back a month I'd have to change the W to P and Z to S. As you can see the letters always move by 7. My cover sheet has about 20 of these formula's so it's a pain if I ever needed to change the month.

What I'm looking to do is create two macro buttons saying Month <-- and Month --->. When you click the macro it would change the formula's to work for the next or previous month. The only way I can think of to do this is to create a macro for each month and then put them inside another macro that groups the month in order. I don't even know if that's possible though. Any help would be greatly appreciated. Thanks

 

[onlyadrafter]

Hello,

Why not name range W4:Z43 as JAN, and name AD4:AG43 as Feb etc,

you can then enter a month in a cell, and use

=VLOOKUP($A$1,INDIRECT(B1),2)

where B1 = a month matching the named range

 

[Oaktree]

=VLOOKUP($A$1,OFFSET(actuals!$W$4:$Z$43,0,7*B1),2)

Where B1 is the months to move left or right from the base of W4:Z43.