| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Entering changing months with a cell
Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4
has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
Maybe
=OFFSET(INDIRECT(ADDRESS(SUMPRODUCT(MAX((ROW(D2:AH 13)*(D2:AH13<"")))),SUMPRODUCT(MAX((COLUMN(D2:AH1 3)*(D2:AH13<"")))))),0,(SUMPRODUCT(MAX((COLUMN($D $2:$AH$13)*($D$2:$AH$13<""))))*-1)+3,1,1) It's a bit long winded and I'm sure there must be an easier way but lets wait and see. Mind out for the line-wrap it's all one line. Mike "Loadmaster" wrote: Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
Try
=INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
Apart from being long-winded it doesn't work (:
Mike "Mike H" wrote: Maybe =OFFSET(INDIRECT(ADDRESS(SUMPRODUCT(MAX((ROW(D2:AH 13)*(D2:AH13<"")))),SUMPRODUCT(MAX((COLUMN(D2:AH1 3)*(D2:AH13<"")))))),0,(SUMPRODUCT(MAX((COLUMN($D $2:$AH$13)*($D$2:$AH$13<""))))*-1)+3,1,1) It's a bit long winded and I'm sure there must be an easier way but lets wait and see. Mind out for the line-wrap it's all one line. Mike "Loadmaster" wrote: Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
It didn't work either.
"Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
The closest I came is when I used the rng D2:AH13, D2 vice D1 and I pushed
Ctrl+Shift+Enter at the end of the formula . it still came up with a month of Feb "which was wrong" and would not change when I entered another figure within the table. "Loadmaster" wrote: It didn't work either. "Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
Try this array formula** :
=INDEX(C2:C13,MATCH(1E100,INDEX(D2:AH13,0,MAX(IF(D 2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))) Curious about this: Cells D1:AJ1 have years 2006:2038. According to that your table extends to column AJ yet the formula you posted only covers up to column AH. -- Biff Microsoft Excel MVP "Loadmaster" wrote in message ... The closest I came is when I used the rng D2:AH13, D2 vice D1 and I pushed Ctrl+Shift+Enter at the end of the formula . it still came up with a month of Feb "which was wrong" and would not change when I entered another figure within the table. "Loadmaster" wrote: It didn't work either. "Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
It worked for me!
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... It didn't work either. "Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
Thank-you that is the formula I was looking for.
"T. Valko" wrote: Try this array formula** : =INDEX(C2:C13,MATCH(1E100,INDEX(D2:AH13,0,MAX(IF(D 2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))) Curious about this: Cells D1:AJ1 have years 2006:2038. According to that your table extends to column AJ yet the formula you posted only covers up to column AH. -- Biff Microsoft Excel MVP "Loadmaster" wrote in message ... The closest I came is when I used the rng D2:AH13, D2 vice D1 and I pushed Ctrl+Shift+Enter at the end of the formula . it still came up with a month of Feb "which was wrong" and would not change when I entered another figure within the table. "Loadmaster" wrote: It didn't work either. "Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
Take a look at T.Valko's reply his reply is what I was looking for.
"Bernard Liengme" wrote: It worked for me! -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... It didn't work either. "Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Entering changing months with a cell
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Loadmaster" wrote in message ... Thank-you that is the formula I was looking for. "T. Valko" wrote: Try this array formula** : =INDEX(C2:C13,MATCH(1E100,INDEX(D2:AH13,0,MAX(IF(D 2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1))))) Curious about this: Cells D1:AJ1 have years 2006:2038. According to that your table extends to column AJ yet the formula you posted only covers up to column AH. -- Biff Microsoft Excel MVP "Loadmaster" wrote in message ... The closest I came is when I used the rng D2:AH13, D2 vice D1 and I pushed Ctrl+Shift+Enter at the end of the formula . it still came up with a month of Feb "which was wrong" and would not change when I entered another figure within the table. "Loadmaster" wrote: It didn't work either. "Bernard Liengme" wrote: Try =INDEX(C2:C13,MAX(IF(D1:AH12<"",COLUMN(D1:AH12)-COLUMN(D1)+1)-1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Loadmaster" wrote in message ... Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4 has the array formula =LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF (D2:AH13<"",COLUMN(D2:AH13)-COLUMN(D2)+1)))) ,showing the last figure entered in the table. Note: I had to push Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the Month corresponding to the last figure entered in D2:AH13 from column C? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| changing from days to months | Charts and Charting in Excel | |||
| changing dates into months | Excel Discussion (Misc queries) | |||
| entering values without changing the function in the cell | Excel Worksheet Functions | |||
| Excel - Changing value of a date by a number of calendar months | Excel Worksheet Functions | |||
| Populate 120 cell column with successive months entering only firs | Excel Worksheet Functions | |||
Linear Mode
