Referencing Last Cell

[jlo]

I have a spreadsheet that has total hours for Jan, Feb, Mar, Apr, May, Jun,
Jul, Aug, Sep, Oct, Nov, Dec. (B21:M21) In these fields are values up until
the current month.

I have a field called Total Hours (B7). How can I get this cell to
reference the current month? Can it be done?

So for this month I keyed 144 in the month of Aug and I want the field Total
Hours to reflect that total automatically. For next month, it should change
to the figure keyedin Sep.

 

[Gaurav]

what is the current formula in B7?

 

[Bob Phillips]

=SUM(B20:INDEX(B21:M21,MONTH(TODAY())))

 

[jlo]

I have nothing in there now. What I do is = and then click on the current
month to get that value. It probably would be easier if I just keyed the
total for the month in that cell. I was hoping to get this to be more
automated.

 

[jlo]

Works great except I don't want a sum, I just want the current value for the
current month. Thanks.

 

[Gaurav]

=LOOKUP(2,1/(B21:M21<>""),B21:M21)

This will return the last (Right Most) value entered in this range.

 

[jlo]

Thank you. You are the best!!! Works like I want it to work!

 

[Gaurav]

You're welcome.

Thank you. You are the best!!! Works like I want it to work!

 

[T. Valko]

If the data is numeric and the *last* entry in the range will be for the
current month:

=LOOKUP(1E100,B21:M21)

Or, you could modify Bob's formula:

=INDEX(B21:M21,MONTH(NOW()))