Daily Calculating

Vnorrell 

The current spreadsheet (attached again below) uses this formula in X39:

=INDEX(O5:O36,MATCH(TODAY()-1,A5:A36,0))+INDEX(R5:R36,MATCH(TODAY()-1,A5:A36,0))

That formula does give yesterday's (the prior day's) total for columns O and R

You had asked that it do that--give the prior day's (most recent day) data rather than the current, because presumably it's actually mailed out at the end of the day or first thing in the morning. Here's your message stating that requesr:

Yes I need the most recent day's data. The report is emailed to the company every day. The report or spreadsheet I posted here was one that was actually emailed to the company on the 7th of January.

However, I also think it's important that you pay attention to this other question that I asked, one of the ones you say you don't want to deal with. It's very important. Because by entering a formula such as the one above into cell X39,  you ARE making a major change and you appear to be unaware of potential consequences. Here's the question again.

Third, a minor question: do you email this out as a "live" spreadsheet? Using the formula I've just given you would be problematic if that is the case and a user doesn't look at it for a day or two....because it would have updated itself to be showing data that didn't exist at the time it was mailed out. So I hope you email out a PDF that freezes the data for the day. But this question also relates to the question about showing all of the daily totals in a new column, so no matter when a given view looks at it, they'd see the actual totals for each day prior to the generation of that given report.

I said it was a "minor question" when I wrote it, but I'm going to reclassify it as major. IF you are sending this out as a live spreadsheet, one with all formulas functioning, then anybody who looks at it on a day later than the day when you send it out, or yet again a day later---they're NOT going to be seeing data in cell X39. It will always be adding up column O and R data from whatever is the day prior to the day they're looking at it. So it will read 0 if they are looking at it two days or more after the last day data were entered. Whereas, if you send it out as a PDF (which is easy to do, but I'm not going to go into that here), then it's "frozen" and will always be accurate for the day shown.

You're to be commended for looking for a solution to make your job easier; I'm sorry to say that it doesn't look like you actually want to make what you do more effective, just easier for you. But unless you give more thought to what does happen down the road, you might be creating more problems for yourself than you realize. You've made pretty clear that you "don't care" about what happens with this once you send it out. Given that, given your lack of interest in thinking through the whole process of which you are a part, I think that it probably makes sense for you to keep doing it as you've been doing it.

In other words:

• Use this formula IF and ONLY IF you send out your report as a PDF.
• Otherwise, you should enter an updated row reference as you've been doing. It will remain stable with the result for that day, no matter when somebody else up the management chain looks at it.