Need help with a dynamic range

[shnelson5068]

I have a formula for a dynamic range of data that represents one number per day for a month. Once the month is over, I am manually updating the range with the cell reference of the first day of the new month. Is there something I can add to my offset formula to automatically roll my named range to the next month? Here is my range:

=OFFSET($I$177,,,COUNTA($I$177:$I$1048576),1)

Cell I177 represents the value on August 1st and the range pulls the data for the entire month (cell I178 has the value for Aug 2nd, etc). Now that we are in September, I need the range to start on Sep 1, so the new formula is

=OFFSET($I$200,,,COUNTA($I$200:$I$1048576),1)

as cell I200 is the value for the Sep 1st data. Would love some help to get the process of rolling months automated.

Thanks!

 

[6StringJazzer]

If you want to go by today's actual date, you can do something like this (tested)

=LET(FOM,INDEX($I:$I,MATCH(EOMONTH(TODAY(),-1)+1,$I:$I,0)),OFFSET(FOM,,,COUNTA(FOM:$I$1048576),1))

This assumes that you will never have data that goes past the current month.

But if today is 9/1 and you need to go back and do August data you will need a different solution.