formula that finds the last row with data in a column

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
457
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
the last cell in column K (the column & row that contains the cell with the data I need) is formated as "custom" in this format: "m/d/yyyy h:mm"

It does what I need it to do (my formula) like this:

="last updated on "&TEXT(CHANGE_LOG!K943,"mmmm dd yyyy")

But instead of "K943", I need the formula to use something like LOOKUP or INDEX to return the last row in column K... i've almost got it to work, but not quite and I'm tired of trying so I thought I'd ask here. :)

this is the last cell in Column K, and how its formatted:

Capture3.PNG
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
How about
Excel Formula:
="last updated on "&TEXT(LOOKUP(2,1/(CHANGE_LOG!K:K<>""),CHANGE_LOG!K:K),"mmmm dd yyyy")
 
Upvote 0
Solution
Upvote 0
Thanks for the link, Joe.

I had actually already found that using google, and it was one of the ones that for whatever reason, I couldn't get to work for me. Here is what it returns using that formula:

View attachment 39040

I also tried some of the other examples on that link and they also returned "0".
Looks like you forgot to put the sheet reference of "CHANGE_LOG" in front of your last reference of "K:K", as shown in Fluff's formula.
 
Upvote 0
the last cell in column K (the column & row that contains the cell with the data I need) is formated as "custom" in this format: "m/d/yyyy h:mm"

It does what I need it to do (my formula) like this:

="last updated on "&TEXT(CHANGE_LOG!K943,"mmmm dd yyyy")

But instead of "K943", I need the formula to use something like LOOKUP or INDEX to return the last row in column K... i've almost got it to work, but not quite and I'm tired of trying so I thought I'd ask here. :)

this is the last cell in Column K, and how its formatted:

View attachment 39038
Assuming there's data in the entire column of K (no blanks), then can use COUNTA to find the last row, and INDIRECT to reference it in your formula (tweak this as needed):

Excel Formula:
="last updated on "&TEXT(INDIRECT("K"&COUNTA(K:K)),"mmmm dd yyyy")
 
Upvote 0
Looks like you forgot to put the sheet reference of "CHANGE_LOG" in front of your last reference of "K:K", as shown in Fluff's formula.
Well son-of-a.... :confused:

you are absolutely correct. I never even noticed that I should of put 'CHANGE_LOG!' (the worksheet reference) twice(!) and I only had it listed before the first "K:K" and not the second "K:K" as well.
Capture.PNG


My bad, sir. Thank you again, though. (y)
 
Upvote 0
You are welcome.

Yes, each range reference is totally independent of one another, so you need to explicitly reference each one.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top