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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?