A function incoprating date and a month/monetary value.

[Guest]

I have a column with the months Jan-Dec. In the next column I have a
corresponding monetary value. I want the monetary value for the current
month to automatically be shown in the cell and for it to update as the year
goes on. I know that I have to recalculate the workbook every day, but
still, how do I make this work?

 

[T. Valko]

Assuming your month names are in the range A1:A12 and are in sequential
order. B1:B12 are numeric values.

=INDEX(B1:B12,MONTH(TODAY()),0)

 

[Guest]

Is there anyway that I can run this equation by incorporating the table
information into it?

 

[T. Valko]

Not sure what you mean by that. Can you explain in greater detail?

 

[Guest]

Right now I have the corresponding data on sheet two. I pull it from there
to the equation on sheet one. Can I input all of the data into the equation
and have it draw the correct data? The hard part is incorporating time as a
self updating factor.

 

[T. Valko]

Sorry, but I'm not following you on this.

If on sheet2 you have:

...........A..........B
1......Jan.........10
2......Feb........22
3......Mar.......17
4......Apr.........6
5......May.......30
6......Jun.........31
7......Jul..........11
8......Aug........10
9......Sep........25
10....Oct........14
11....Nov.........0
12....Dec.......41

Then on sheet 1 you have this formula:

=INDEX(Sheet2!B1:B12,MONTH(TODAY()))

The result is 25 because today's date is in the month of September.

The hard part is incorporating time as a self updating factor.

What does TIME have to do with it? You could use this and it will do the
exact same thing:

=INDEX(Sheet2!B1:B12,MONTH(NOW()))

TODAY is based on today's date
NOW is based on today's date plus the time (based on the last time that a
calculation took place).

 

[Guest]

That is how I have it set-up. I am just wondering if it is possible to
eliminate sheet two, incorporate the data into the equation and use time as
the changing factor to give the correct data. I have a =NOW() in sheet1!A1.

 

[Peo Sjoblom]

In the formula bar select this part

Sheet2!B1:B12

press F9 and enter and will be hard coded into your formula


--

Regards,

Peo Sjoblom

 

[T. Valko]

Oh, I see what you mean!

Based on the 12 values I used in my other reply:

=INDEX({10,22,17,6,30,31,11,10,25,14,0,41},MONTH(A1))