Display Current Month Data

[brorick]

Hello Everyone. I attempted to get the highlighted fields in my attached Excel workbook to reflect the current month data. For instance, if this was early December then the data would reflect November's data and in January the information would reflect December's data. I attempted to use two formulas with little success. Unfortunately, when I use =MONTH((TODAY())-25), my cell reflects January and I need it to reflect December. My other formula =HLOOKUP(T1,dataarea,7,TRUE) works successfully for the current month but not when I attempt to refer to the previous month due to the other formula not working properly.

I would love to do this in vba or maybe someone knows how to modify the formula on the attached workbook so it works properly. Any help is greatly appreciated. Thank you in advance.

[WillR]

Can you describe the results you want to appear in the highlighted cells as it's not too clear from the file

[brorick]

Hello WillR. Based on today's date the following highlighted cells should reflect Decembers data. Example based on attached workbook: Q10 = 288, Q19 = 0, Q21 = 0, Q22 = 0, Q23 = 0. In the month of February the data would reflect January's data.

Not as important but would be nice to include. Since this is a report that will be saved based on the month it reflects. I am not sure how once the data is saved, the data continues to show as it was the day it was saved. Kind of like a Crystal Report. You get the option to refresh data or use the existing data. Because the formula will reflect the most previous months data everytime the user access the workbook, let's say the user views the July report during the month of October, the user will see the previous months data in the total column and not July's data. Thanks for your help.

[WillR]

OK

See the attached file.

The formula in T2 is

=MONTH(EOMONTH(T1,-1))

to return the interger (1-12) of the prior month.

The formula in Q10 becomes

=OFFSET(C10,0,$T$2)

Thus returning the previous month values.

I have attached a couple of simple macros to enable you to Save/Refresh. See the attached file.

[brorick]

WillR, thank you for your assistance. I can now see the logic which you have applied. I will give it a try. You are the best! Have a great day!