G
Guest
Is there a way to select the next to last non-empty cell in a row?
Thank you.
Thank you.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Cong,
Try Ctrl+right arrow, let go of the Ctrl key and press left arrow.
Hope this helps.
Try Ctrl+right arrow, let go of the Ctrl key and press left arrow.
Hope this helps.
G
Guest
Ellen,
Thanks for the tip, but I need a formula.
Thanks for the tip, but I need a formula.
G
Guest
Lets say the last filled cell in row #9 is K9
and that J9 contains 5. Then
=WhereIsIt(9) will display $J$9 - the location
of the next to the last filled cell in row 9
=WhatsInIt(9) will display 5, the content of that cell.
Here is the code:
Function WhereIsIt(n As Long) As String
m = Cells(n, Columns.Count).End(xlToLeft).Column
WhereIsIt = Cells(n, m - 1).Address
End Function
Function WhatsInIt(n As Long) As Variant
m = Cells(n, Columns.Count).End(xlToLeft).Column
WhatsInIt = Cells(n, m - 1).Value
End Function
and that J9 contains 5. Then
=WhereIsIt(9) will display $J$9 - the location
of the next to the last filled cell in row 9
=WhatsInIt(9) will display 5, the content of that cell.
Here is the code:
Function WhereIsIt(n As Long) As String
m = Cells(n, Columns.Count).End(xlToLeft).Column
WhereIsIt = Cells(n, m - 1).Address
End Function
Function WhatsInIt(n As Long) As Variant
m = Cells(n, Columns.Count).End(xlToLeft).Column
WhatsInIt = Cells(n, m - 1).Value
End Function
D
Domenic
If the data contains numerical values...
=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A100,MATCH(9.99999999999999E+30
7,A2:A100)-1))
If the data contains text values...
=LOOKUP(REPT("z",255),A2:INDEX(A2:A100,MATCH(REPT("z",255),A2:A100)-1))
Adjust the ranges accordingly.
Hope this helps!
=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A100,MATCH(9.99999999999999E+30
7,A2:A100)-1))
If the data contains text values...
=LOOKUP(REPT("z",255),A2:INDEX(A2:A100,MATCH(REPT("z",255),A2:A100)-1))
Adjust the ranges accordingly.
Hope this helps!
G
Guest
Hi Gary"s Student,
Sorry about the not-clear question.
I want a cell in another tab shows the content of the next to last non-empty
cell of a row from a different tab, i.e., $J$9
Thank you.
Sorry about the not-clear question.
I want a cell in another tab shows the content of the next to last non-empty
cell of a row from a different tab, i.e., $J$9
Thank you.
G
Guest
Domenic,
The formula for text gives the last non-empty data, not the previous.
Thank you
The formula for text gives the last non-empty data, not the previous.
Thank you
D
Domenic
Does the data contain formula blanks ("") ? If so, try the following
formula instead...
=INDEX(A2:A100,LARGE(IF(A2:A100<>"",ROW(A2:A100)-ROW(A2)+1),2))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.
Hope this helps!
formula instead...
=INDEX(A2:A100,LARGE(IF(A2:A100<>"",ROW(A2:A100)-ROW(A2)+1),2))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.
Hope this helps!
G
Guest
Domenic,
Thank you
--
Cong Nguyen
(e-mail address removed)
Thank you
--
Cong Nguyen
(e-mail address removed)
Domenic said:Does the data contain formula blanks ("") ? If so, try the following
formula instead...
=INDEX(A2:A100,LARGE(IF(A2:A100<>"",ROW(A2:A100)-ROW(A2)+1),2))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.
Hope this helps!