Last column with an entry

  • Thread starter Thread starter Gef
  • Start date Start date
G

Gef

I have a list consisting of 12 columns and > 20 rows (say
A1:L20 with headings in row 1. For each row of data I
need to know the heading of the last column with an entry
e.g if on row 4 the last column with an entry is K (K4) I
need to know the value of K1

Values could be placed in col 13 (M) for each row.

Any ideas would be appreciated
 
Hi
if there are no blank clls in between enter the following in M2
=OFFSET($A$1,0,COUNTA(A2:K2)-1)
and copy down
 
Thanks Frank - but what if there are blank cells?
basically the table fills from the left (say each month)
so the number of blank right hand columns will gradually
get less
 
Hi
no problem if the cell fills from the left. You only get wrong results
for blank cells in between. e.g. A2 is filled and C2 is filled but B2
is blank
 
And if by any chance you could potentially have blank cells, you can use:-

=OFFSET($A$1,,SUMPRODUCT(MAX(COLUMN($A2:$L2)*($A2:$L2<>"")))-1)
 
Thanks - this has cured the spaces problem.

-----Original Message-----
And if by any chance you could potentially have blank cells, you can use:-

=OFFSET($A$1,,SUMPRODUCT(MAX(COLUMN($A2:$L2)*
Click to expand...
 
Back
Top