Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Function
D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14
total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for the year 2010 is 0.0 as we have not entered into that year as of yet. The same for the rest of the row up to AJ14. I am looking for a formula that will enter in cell B35 the total accumulated hours of just the current year from D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter 0.0 from cell AJ14. How can I get it to read the current yearly hours from Dec 2009 in cell G14? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Function
Hi,
Possibly this =LOOKUP(2,1/(D14:AJ140),D14:AJ14) Mike "Loadmaster" wrote: D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14 total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for the year 2010 is 0.0 as we have not entered into that year as of yet. The same for the rest of the row up to AJ14. I am looking for a formula that will enter in cell B35 the total accumulated hours of just the current year from D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter 0.0 from cell AJ14. How can I get it to read the current yearly hours from Dec 2009 in cell G14? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Function
Hi,
It looks to me like you should use =INDEX(D14:AJ14,,MATCH(YEAR(NOW()),D1:AJ1,)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Loadmaster" wrote: D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14 total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for the year 2010 is 0.0 as we have not entered into that year as of yet. The same for the rest of the row up to AJ14. I am looking for a formula that will enter in cell B35 the total accumulated hours of just the current year from D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter 0.0 from cell AJ14. How can I get it to read the current yearly hours from Dec 2009 in cell G14? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Function
Thank-you it does work.
"Mike H" wrote: Hi, Possibly this =LOOKUP(2,1/(D14:AJ140),D14:AJ14) Mike "Loadmaster" wrote: D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14 total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for the year 2010 is 0.0 as we have not entered into that year as of yet. The same for the rest of the row up to AJ14. I am looking for a formula that will enter in cell B35 the total accumulated hours of just the current year from D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter 0.0 from cell AJ14. How can I get it to read the current yearly hours from Dec 2009 in cell G14? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP Function
Hi,
The LOOKUP version would be =LOOKUP(YEAR(NOW()),D1:AJ1,D14:AJ14) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Loadmaster" wrote: D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14 total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for the year 2010 is 0.0 as we have not entered into that year as of yet. The same for the rest of the row up to AJ14. I am looking for a formula that will enter in cell B35 the total accumulated hours of just the current year from D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter 0.0 from cell AJ14. How can I get it to read the current yearly hours from Dec 2009 in cell G14? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Lookup function and Sum function | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |