need to display most recent 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.

 

[SkipVought]

Hi,

One MAJOR problem is that you are STORING data in the way that you want to REPORT the data. It is a typical mistake that novice spreadsheet users make. It randers the data, a very useful corporate asset, much less useful than it the data were stored in a properly designed table.

What happens, for instance, at the beginning of the next month. Tilt with a seamless solution, I fear.

That shortcoming aside, and not knowing hardly anything about the actual layout of your sheet, assuming that your numeric data begins in row 2 column B and that there is a VALUE for each day already passed (even if the value is ZERO), in the SUMMARY column (AG)...
[tt]
=index(b2:af2,1,count(b2:af2))
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[macropod]

Hi Jaffey ,

What happens after you've got 255 days' data? Assuming you're not using Excel 2007, you've only got 256 columns to work with, and one of those has the agent names. There are 261 weekdays most years and, even if holidays and weekends aren't used, you've got barely enough columns for a single year.

If you had the agent names across the top row (assuming you've no more than 255 agents), and the data for each day on a separate row, you could put over 179 years' data on a single worksheet - including holidays and weekends.

Summarising and transposing such data for reporting is a trivial exercise.

Cheers

[MS MVP - Word]

 

[Jaffey]

Thanks Skip! Your formula works great. I agree completely with your comments but unfortunately they have been doing it this way for many ways.
One question, your formula returns an error if none of the cells in a row are populated. This happens from time to time when agents take time off etc. and there is no data to report. This error is preventing me from summing the numbers in the column.

 

[SkipVought]

use the ISNA function in an IF to test and return an appropriate value.

Skip,
[sub]
[red][/red]
[/sub]