how show yesterday's data only

[Jaffey]

I have a table that shows how much revenue each agent brings in each day.
The agent names are in the first column and the days of the month are labelled across the top in the first row.
Each day the previous day's revenue numbers are entered in the column for that day.
I would like to have a column in a summary report that always shows "yesterday's" revenue figures. My problem is, how does I write a formula that knows what day had the most recent entries. Simply hard referencing Today()-1 won't work because on Monday's it would try to look up Sunday and there would be nothing there. What I really need is a formula that can tell what is the right most column that has been populated and give me that data. Am I making this clear enough? Many thanks.

 

[PHV]

You've posted in the VBA forum.
For formula issues, please, post here: forum68

 

[Jaffey]

thanks, I thought the problem might require a VBA solution as opposed to a formula

 

[PHV]

what is the right most column that has been populated
With ActiveSheet.UsedRange
LastCol = .Columns.Count + .Column - 1
End With


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886