Change cell value based on system month

[Icesurf3r]

Hi All,

I am trying to automatically update some year to date figures (from previous years) on a worksheet (Sheet 1), using the system date/month.

I have a range of values in A1 - A12 (one for each month Jan-Dec) on a hidden sheet, Sheet 2. What I need to accomplish is for cell R4 on Sheet 1 to display the value from A1 on Sheet 2 while in January, and for this to automatically update to the value from A2 on Sheet 2 once in February, A3 once in March etc.

Hoping someone here will be able to help.

 

[Scott T]

What are the values in Sheet2 are there dates in Sheet2?

 

[Icesurf3r]

Thanks the quick reply Scott.

Sorry for my ignorance, but maybe I'm not understanding the formula correctly. I don't need to display the month name. All I need is for the value of A1 on Sheet 2 to be displayed in cell R4 on sheet 1 during January, then for R4 on Sheet 1 to update to A2 on Sheet 2 during February etc.

Hopefully that's made things a little clearer.

 

[PaddyD]

Use an index(), with the row variable derived from month(today())

 

[Tetra201]

Try this:

=INDEX(Sheet2!A:A,MONTH(TODAY()))

 

[Scott T]

Tetra has a better formula

 

[Icesurf3r]

Try this:

=INDEX(Sheet2!A:A,MONTH(TODAY()))

This gives an invalid cell reference error.

The data in rows A1 - A12 is purely numerical, A1 = 32, A2 = 22, A3 = 10 etc. I can add the months if this is the problem?

Not sure if I'm doing something wrong, but on entering the formula and pressing enter, excel opens a pop up explorer window labelled Update Values: Sheet 2. It's almost like it's expecting me to browse to another excel file and open it.

 

[Scott T]

Add the month number in sheet2 column B B1=1 B2=2 etc.

=INDEX(Sheet2!A1:A12,MATCH(MONTH(A2),Sheet2!B1:B12,0))

 

[Tetra201]

This gives an invalid cell reference error...

... on entering the formula and pressing enter, excel opens a pop up explorer window labelled Update Values: Sheet 2...

This means you have no worksheet named "Sheet2" in your workbook.

=INDEX('Your_Sheet_Name_Here'!A:A,MONTH(TODAY()))