epd
05-26-2013, 03:18 PM
...I hope that heading made sense.
Refer to the below image.
Hi, I'm trying to have column C display items in row 3 under the TRANS heading based on the last cell in a range that is populated.
So for example, C10 should read E as the last populated cell in range E10:K10 is I10....which falls under heading E.
This is the code I have for D10:
=LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10)
This shows me the value of the last populated cell in that specified range.
Having that code, I assumed OFFSET(LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10),-B10,0,1,1) would work, before realizing that I'm not getting the cell location, but rather the value.
I assume I'm correctly using OFFSET(***,-B10,0,1,1).
To manually get the TRANS sub heading value I would use OFFSET(I10,-B10,0,1,1). But how should my code look if I want it to manually work out the last populated cell in range E10:K10 and then give me the TRANS sub heading value?
http://imageshack.us/a/img850/8243/helpzp.png
Thank you
This has also been posted here (http://www.mrexcel.com/forum/excel-questions/702496-getting-cell-coordinate-last-cell-text-ranged-row.html#post3469701)
Refer to the below image.
Hi, I'm trying to have column C display items in row 3 under the TRANS heading based on the last cell in a range that is populated.
So for example, C10 should read E as the last populated cell in range E10:K10 is I10....which falls under heading E.
This is the code I have for D10:
=LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10)
This shows me the value of the last populated cell in that specified range.
Having that code, I assumed OFFSET(LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10),-B10,0,1,1) would work, before realizing that I'm not getting the cell location, but rather the value.
I assume I'm correctly using OFFSET(***,-B10,0,1,1).
To manually get the TRANS sub heading value I would use OFFSET(I10,-B10,0,1,1). But how should my code look if I want it to manually work out the last populated cell in range E10:K10 and then give me the TRANS sub heading value?
http://imageshack.us/a/img850/8243/helpzp.png
Thank you
This has also been posted here (http://www.mrexcel.com/forum/excel-questions/702496-getting-cell-coordinate-last-cell-text-ranged-row.html#post3469701)