conditional formatting (or VBA?) to go to a specific row number based on the current date

[kbishop94]

column K and L on my worksheet works like this...

Column K represents an employee count for each criteria (facility, area, whatever...)

Column L shows what the change is in the count from the previous month. The previous month data is found in the row number for the current month (kinda confusing, but stay with me... the screenshot will allow this to make sense...) (hopefully)



so row number 198 represents the current month (August.)

So I need code (or conditional formatting rule) to go to the specific row number based on what the current month is.

As you can see in column B, the next upcoming month (Sept) is going to be row # 199. So the code in cell L3 will change from 198 to 199 whenever Sept 1st rolls around... (does that make sense?)

So what I need is some kind of 'If' statement where if current date now() = Sep-2024, then row number is 199. when now() is going to = Oct-2024, then the row number will be 200... and so on.

How can I do something like this? Thank you

 

[6StringJazzer]

So the code in cell L3 will change from 198 to 199 whenever Sept 1st rolls around... (does that make sense?)

It made sense after reading your complete description and studying your screenshot. However, your title is misleading because 1) you do not want to "go to" a row, you want to reference it, and 2) this requires a formula, not conditional formatting or VBA.

This solution assumes that the values in B192: B202 are actually date values. I am assuming they are the first day of the month (e.g., B191 is 1/1/2024). If not, you'll have to change them. The formula for L4 (not L3 as in the quote above) will be

=XLOOKUP(EOMONTH(TODAY(),-1)+1, $B$192: $B$202, $E$192: $E$202)

This will be based on what the date is when the last calculation was performed on the sheet, not the "last updated" date at the top. So if today is Sept 1 and the last update was Aug 31, it will show the data from the September row.

 

[kbishop94]

Thank you for both your solution and for correcting how I should of titled my post due to my misleading nomenclature. I should of known that... (I DO know that, actually(!)... I'm not sure why I called it what I did, but I wont make that mistake again.)
Thank you again for your all your help.

 

[6StringJazzer]

Not a big deal, just that if your title is clear you are most likely to get the best help.

Anyway, did my formula work for you?

 

[kbishop94]

Yea! It worked perfectly! It was a great solution my friend!