# Macro Question

#### Weasel

##### Board Regular
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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

=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.

Replies
10
Views
244
Replies
2
Views
191
Replies
18
Views
421
Replies
9
Views
164
Replies
4
Views
208

1,219,997
Messages
6,151,366
Members
451,023
Latest member
dwalc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back