Macro Question

Weasel

Board Regular
Joined
Sep 13, 2005
Messages
58
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
=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.
 

Forum statistics

Threads
1,136,369
Messages
5,675,361
Members
419,565
Latest member
Phil57

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top