If column has entry, then insert date from top

tarmand

Board Regular
Joined
Jul 11, 2002
Messages
197
I need help. I have a report that I keep forgetting to change the week ending date. I need to tell the date cell to look in rows 8 through 75 and find the farthest column that has an amount, then grab the date from row 6 of that column and insert it in the date cell (J7).

Essentially, I have this spreadsheet that lists projects by row and every week, I update the expenditures over to the right (April 16 information was in column "AM"). Some projects will have expenditures, and some won't. But I need the date to show the most recent week ending date entered.

Can someone help me with this?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Here's a custom User Defined Function (UDF) that should do what you want.

To install the UDF...
  • Alt+F11 to open the VBA editor
  • From the VBA menu, select Insert\Module
  • Paste the code below in the VBA edit window
  • Back in Excel, enter this formula in cell J7
=most_recent_week_ending_date(8:75)

8:75 are the rows to check for the last used column

Code:
Function most_recent_week_ending_date(rng As Range)
    Dim LastCol As Long
    LastCol = rng.Find("*", , , , xlByColumns, xlPrevious).Column
    most_recent_week_ending_date = Cells(6, LastCol).Value
End Function
 
Last edited:
Upvote 0
Thanks for your help. But, something is off. The date should show up as 04/16/11 and it is showing 10/01/11. I suck at macros, so I have no idea what your macro is telling it to do. But, it should be looking for the last colum (rows 8 to 75) that has data in it and grab the date from row 6 of that column. Is that what it says to do?
 
Upvote 0
Nevermind. Someone had done some calculating in that column. When I deleted it, it worked fine. Thanks!!!!!!!
 
Upvote 0
I want to do this again, but I need it to find the last column on worksheet "expenditures" with data and return the data in a specific row of that column (depending on where I insert the function).

For Instance, If I post this on the SMM worksheet in "A7", I will always need it to pull the data from row 94 of the expenditures sheet (from the last column with data)

Can anyone tell me what I need to do?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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