Create Macro to Increase Cell Reference
 

First post here and I thank everyone in advanced that will help out!

I've searched for this all day without any luck. Maybe I'm just overlooking it, or just really don't understand.

Anyways, I have an Excel sheet called "Snapshot" that has averages from a sheet called "Raw Data (I&R)". So, here is what I need some help with. I would like to create a Macro with either a keyboard shortcut or upon opening the doucment (it really doesn't matter) so that the "cell" will change using the macro. Notice the formula below, it's pulling the average from column "B" cell position "B177", so tomorrow, when I go back in, by using the macro, it would increase the cell position to "B178"so on and so on.

Example of the formula:
=AVERAGE('Raw Data (I&R)'!B2:B177)

Hopefully I explained this well enough.

Thanks,
Nick

No need to use a macro in this one. You can use the formula:

=AVERAGE(OFFSET('Raw Data (I&R)'!$B$2,0,0,COUNTA('Raw Data (I&R)'!$B:$B)-1,1))

The formula will adjust the range automatically.

I really appreciate your response Wickedchew as it does work. However, it's averaging the entire column of data, and I would like it to average the past 30 days of data and shift everyday. Is this possible? Thanks again!

Let's say your table has the following information:

Column B has all the dates
Column C has all the data
Cell A1 has the start date to be averaged
Cell A2 has the end date to be averaged

=AVERAGE(OFFSET(C1,MATCH(A1,B:B,0)-1,0,(A2-A1)+1,1))

If you want it just plain 30 days from the start date:

=AVERAGE(OFFSET(C1,MATCH(A1,B:B,0)-1,0,30,1))