0

I've searched for an answer, but it seems that I can only find VBA-based solutions, which appear no longer be an option in Excel 2008.

I'm trying to return the name of the last cell in a row that contains data. It looks like this:

+===========================================================+
                 A      /  B  /  C  /  D  /  E  (columns)
+===========================================================+
1         || [Formula] /  3  /  4  /  5  / [blank]
2         || [Formula] /  7  /  8  / [blank] / [blank]
3         || [Formula] /  9  / 10 / 11 / 12
(rows)
+===========================================================+

and the end result would look like this:

+===========================================================+
             A   /  B  /  C  /  D  /  E  (columns)
+===========================================================+
1         || D1 /  3  /  4  /  5  / 
2         || C2 /  7  /  8  /     / 
3         || E3 /  9  / 10 / 11 / 12
(rows)
+===========================================================+
pnuts
  • 58,317
  • 11
  • 87
  • 139
KumbaThought
  • 183
  • 2
  • 3
  • 10

4 Answers4

1

It looks like your data is sorted from left to right.
If it is, you could start with something like =MATCH(MAX(C4:K4);C4:K4) to retrieve the column index of the last cell (max) of the row. From there, it's easy to buid the address:
=ADDRESS(ROW();MATCH(MAX(C1:K1);C1:K1);4)

Edit: combining ADDRESS with COUNT or COUNTA (great idea from Dustin Geile) does not require the items to be sorted: 

=ADDRESS(ROW();COLUMN()+COUNT(B1:Z1);4)
iDevlop
  • 24,841
  • 11
  • 90
  • 149
1
=CHAR(COUNTA(B1:ZZ1) + 65) & ROW()
Dustin Geile
  • 43
  • 4
  • This solution doesn't actually return the correct column. It's off by 1... I would up using: =CELL("address",INDEX(B21:IV21,MATCH(2,INDEX(1/(B21:IV21<>""),0)))) – KumbaThought Sep 13 '11 at 15:30
0

You can try this arrayformula:

{=ADDRESS(ROW(B2),MAX(IF(ISEMPTY(B2:H2),0,COLUMN(B2:H2))))}

Validate with CtrlShiftEnter

JMax
  • 26,109
  • 12
  • 69
  • 88
0

If you are looking for the last cell with data, you can write a quick function and place it in a VBA module in your workbook.

Public Function LastCell(inRange as Range) LastCell = inRange.End(XlToRight).Address End Function

Or just omit the '.Address' if you want to get a range object pointing to the cell.

You can then use it in a formula like...

=LastCell(A1)

Regards Ray

raeldor
  • 503
  • 3
  • 11