How to return the formula and/or cell reference for the last cell in a column?

[BillPeterson]

I'm using the following to give me the last value in a column from the "Checking" tab.

=LOOKUP(2,1/(Checking!D:D<>""),Checking!D:D)

How can I get info on whatever cell that data comes from? In this particular case the formula for that cell is as follows:

=Checking!D2587

If I could just get the cell reference, I could rebuild the formula using INDIRECT. Alternatively, maybe there's a way to just display the formula itself? Thanks in advance for your help! You guys rock!

 

[DanteAmor]

This is what you need?

=INDIRECT("Checking!D" & LOOKUP(2,1/(Checking!D:D<>""),ROW(Checking!D:D)))

 

[Fluff]

How about

=LOOKUP(2,1/(Checking!D:D<>""),formulatext(Checking!D:D))

 

[BillPeterson]

This is what you need?

=INDIRECT("Checking!D" & LOOKUP(2,1/(Checking!D:D<>""),ROW(Checking!D:D)))

This returns the value itself, essentially producing the same result as: =LOOKUP(2,1/(Checking!D:D<>""),Checking!D:D)

What I would like to know is which row the last value is on. Thank you for the help regardless!!!

 

[BillPeterson]

How about

=LOOKUP(2,1/(Checking!D:D<>""),formulatext(Checking!D:D))

This returns "#N/A" as there is only a value there. How can I get the row number? Thank you for the help!

 

[BillPeterson]

This is what you need?

=INDIRECT("Checking!D" & LOOKUP(2,1/(Checking!D:D<>""),ROW(Checking!D:D)))

Yes!! It contains what I was looking for and helped me achieve the objective, which is below. Thank you!!

=CONCATENATE("'=Checking!D"&LOOKUP(2,1/(Checking!D:D<>""),ROW(Checking!D:D)))

 

[Fluff]

This returns "#N/A" as there is only a value there.

Yes, but you asked to return the formula in the cell.

Glad you sorted it & thanks for the feedback.

 

[DanteAmor]

Yes!! It contains what I was looking for and helped me achieve the objective, which is below. Thank you!!

in that case

="Checking!D" & LOOKUP(2,1/(Checking!D:D<>""),ROW(Checking!D:D))