| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Last Cell
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
what is the current formula in B7?
"jlo" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
=SUM(B20:INDEX(B21:M21,MONTH(TODAY())))
-- __________________________________ HTH Bob "jlo" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
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. "Gaurav" wrote: what is the current formula in B7? "jlo" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
Works great except I don't want a sum, I just want the current value for the
current month. Thanks. "Bob Phillips" wrote: =SUM(B20:INDEX(B21:M21,MONTH(TODAY()))) -- __________________________________ HTH Bob "jlo" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
=LOOKUP(2,1/(B21:M21<""),B21:M21)
This will return the last (Right Most) value entered in this range. "jlo" wrote in message ... Works great except I don't want a sum, I just want the current value for the current month. Thanks. "Bob Phillips" wrote: =SUM(B20:INDEX(B21:M21,MONTH(TODAY()))) -- __________________________________ HTH Bob "jlo" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
Thank you. You are the best!!! Works like I want it to work!
"Gaurav" wrote: =LOOKUP(2,1/(B21:M21<""),B21:M21) This will return the last (Right Most) value entered in this range. "jlo" wrote in message ... Works great except I don't want a sum, I just want the current value for the current month. Thanks. "Bob Phillips" wrote: =SUM(B20:INDEX(B21:M21,MONTH(TODAY()))) -- __________________________________ HTH Bob "jlo" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
You're welcome.
"jlo" wrote in message ... Thank you. You are the best!!! Works like I want it to work! "Gaurav" wrote: =LOOKUP(2,1/(B21:M21<""),B21:M21) This will return the last (Right Most) value entered in this range. "jlo" wrote in message ... Works great except I don't want a sum, I just want the current value for the current month. Thanks. "Bob Phillips" wrote: =SUM(B20:INDEX(B21:M21,MONTH(TODAY()))) -- __________________________________ HTH Bob "jlo" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing Last Cell
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())) -- Biff Microsoft Excel MVP "jlo" wrote in message ... Works great except I don't want a sum, I just want the current value for the current month. Thanks. "Bob Phillips" wrote: =SUM(B20:INDEX(B21:M21,MONTH(TODAY()))) -- __________________________________ HTH Bob "jlo" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a cell. | Excel Discussion (Misc queries) | |||
| Referencing Cell Next To Today's Date Cell | Excel Discussion (Misc queries) | |||
| cell referencing | Excel Worksheet Functions | |||
| Cell Referencing | Excel Discussion (Misc queries) | |||
| referencing a name from a cell value | Excel Discussion (Misc queries) | |||
Linear Mode
