Latest Day (MAX across Multiple Sheets)

[TesseractE]

I need to be able to find the most recent date value across multiple sheets based on a given input to look up. I've seen a few methods to do this, but I haven't had any luck adapting them to my project, which requires a LOT of flexibility in where this value might be.

Here's what I've got so far:

=SUMPRODUCT(MAX(IF(INDIRECT("'"&INDIRECT("'Overview'!$K$17:$K$"&((MATCH("Total",'Overview'!$K$1:$K$100,0)-1)-(COUNTBLANK(INDIRECT("'Overview'!$K$17:$K$"&MATCH("Total",'Overview'!$K$1:$K$100,0))))))&"'!$G$1:$G$1000")=D14,INDIRECT("'"&INDIRECT("'Overview'!$K$17:$K$"&((MATCH("Total",'Overview'!$K$1:$K$100,0)-1)-(COUNTBLANK(INDIRECT("'Overview'!$K$17:$K$"&MATCH("Total",'Overview'!$K$1:$K$100,0))))))&"'!$D$1:$D$1000"))))

The list of Sheet Names is held in the array delineated by the following snippet. The Names are in a table that goes from Overview!K17 to Overview!K34 (With "Total" in Overview!K35), but I had to cut out the empty spaces to avoid errors. This will dynamically find the precise range of the names, cutting out those spaces.

INDIRECT("'Overview'!$K$17:$K$"&((MATCH("Total",'Overview'!$K$1:$K$100,0)-1)-(COUNTBLANK(INDIRECT("'Overview'!$K$17:$K$"&MATCH("Total",'Overview'!$K$1:$K$100,0))))))

D14 is the name that I need to base the search on, and the rest of it is SUPPOSED to search every sheet the name in D14 appears in (in Column G) and return the highest value in Column D (Last Contact Date) from all of those entries.

For example, if you have a contact date of 2/14 in Sheet 1 for 'TesseractE', 1/30 in Sheet 2 for 'TesseractE' and 2/19 in Sheet 3 for 'TesseractE', it will return the value of 2/19.

I'm basing this attempt off of a similar formula that works perfectly well to SUMIF across the multiple sheets:

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDIRECT("'Overview'!$K$17:$K$"&((MATCH("Total",'Overview'!$K$1:$K$100,0)-1)-(COUNTBLANK(INDIRECT("'Overview'!$K$17:$K$"&MATCH("Total",'Overview'!$K$1:$K$100,0))))))&"'!$G$1:$G$1000"),D14,INDIRECT("'"&INDIRECT("'Overview'!$K$17:$K$"&((MATCH("Total",'Overview'!$K$1:$K$100,0)-1)-(COUNTBLANK(INDIRECT("'Overview'!$K$17:$K$"&MATCH("Total",'Overview'!$K$1:$K$100,0))))))&"'!$C$1:$C$1000")))

I know there's no such thing as 'MAXIF', but the 'MAX(IF(' is something I had seen suggested elsewhere.

Any suggestions?

 

[Fazza]

Hi.

Suggestions? Sure,

• have well structured data in normalised tables
• use Excel's database type functionality

If you have lots of data, avoide formulas like SUMPRODUCT & INDIRECT. Maybe even avoid all formulas and use pivot tables or similar.

And if you provide sample data it enormously helps to understand the requirement. Without sample data, it is much more difficult to provide specific advice.

regards

 

[TesseractE]

I'll work on getting you some Sample Data... I'd need to 'sanitise' it first so there's no information on it that could get me in trouble. XD

The trick that keeps me from using something like Pivot Tables is that each line will have some 'downstream' data (Entered on the 'Master' sheet and retrieved on an 'Agent' sheet) and some 'upstream'. In my experience (so far), Pivot Tables haven't been good at doing anything but collecting information - not organising it and returning it into formulae.

What do you mean by 'database functionality'? Are you're referring to formulae like INDEX and VLOOKUP or something entirely different?

And I really do apologise for the messiness of these formulae. I'm still very much in the learning phase on Excel... I've only been doing this stuff since about Sept 2011.

 

[Fazza]

I might be off track with my thinking on this. I'm assuming you have a reasonable amount of data and want to do some database type work with it. If not, then really I can't help much with this question as it sounds like poorly set up data and that will greatly complicate any work.

If my assumption is correct then the first step is to set the data up well. http://en.wikipedia.org/wiki/Database_normalization discusses data normalisation.

For well set up data there is lots of smart functionality - without formulas at all. Formulas like proposed can be slow/inefficient. For lots of data pivot tables, or query tables, or VBA using advanced filtering, or recordsets, or queries with SQL, etc.

regards

 

[TesseractE]

I might be off track with my thinking on this. I'm assuming you have a reasonable amount of data and want to do some database type work with it. If not, then really I can't help much with this question as it sounds like poorly set up data and that will greatly complicate any work.

If my assumption is correct then the first step is to set the data up well. http://en.wikipedia.org/wiki/Database_normalization discusses data normalisation.

For well set up data there is lots of smart functionality - without formulas at all. Formulas like proposed can be slow/inefficient. For lots of data pivot tables, or query tables, or VBA using advanced filtering, or recordsets, or queries with SQL, etc.

regards

Thanks! Whether I can use that with this project or not, it should be useful knowledge for future reference.

This project doesn't have a lot of data, really... The trick here is that I'm working on this sheet for use by other people who will just want to plunk the data where it's most efficient for them to put it in the sheet, so I have to code the workbook to find the data wherever it's put.

I've actually come up with a workaround to get the project out the door in time for March's reports, but I'd still like to get this nailed down for the future.

Lastly, I can't use the nifty Mr Excel HTML conversion tool while at work, but I should be able to make a suitable mock-up when I get home and post it here so you can better visualise what I'm trying to do.

 

[TesseractE]

OK, so this is a quick look at what I'm trying to do.

As I said above, I want to work out a formula for Cell A2 on the 'Master' Sheet that would find latest date for 'Rep One' across all Agent sheets no matter which row it might be in on any give Agent's sheet.

Is this helpful?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #FFC000;;">Latest Date</td><td style="font-weight: bold;text-align: center;background-color: #FFC000;;">Rep Names</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2/21/2012</td><td style=";">Rep One</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #808080;;">(Desired results entered manually)</td><td style="text-align: center;color: #808080;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Master</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #FFC000;;">Latest Date</td><td style="font-weight: bold;text-align: center;background-color: #FFC000;;">Rep Names</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2/10/2012</td><td style=";">Rep One</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Agent One</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #FFC000;;">Latest Date</td><td style="font-weight: bold;text-align: center;background-color: #FFC000;;">Rep Names</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2/21/2012</td><td style=";">Rep One</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Agent Two</p><br /><br />

 

[Fazza]

Well, per my very first point, the data isn't well structured. This complicates doing this sort of query. I don't work with such data.

Consider: what if all the data was on one worksheet? Like it is now (maybe) with one extra column "Agent". Then you might find what you want is MUCH easier. Maybe even a pivot table - so no formulas required. Untested, btw.

Instead of having poorly structured data & needing workarounds or tricks or complex formulas, instead have well structured data and everything you need to do with the data should be much simpler. I've been writing this since the first post: I've posted a link: I feel that the message isn't getting through: I'll leave this thread to others: I have other priorities at the moment.

regards

 

[TesseractE]

Sorry this is frustrating you... Trust me when I say that this is not simply complicated for complication's sake.

Each Agent does need to have his or her own sheet for tracking purposes. The date is the last time an Agent audited the Rep indicated. Sometimes Reps can be reassigned to Agents part-way through the month. When that happens, the old entry on the first Agent's sheet has to stay there, or it'll throw off the reporting for that first agent. The samples above are not ALL of the information needed... Just enough to demonstrate what I need to do.

The workaround I mentioned is that I added a 'Current Agent' and 'Original Agent' on the Master sheet. The SUM worked just fine, so all of the audits performed by all Agents for that Rep will be totalled just fine. Then I just use a formula taking the 'Last Audited' date from the sheet belonging to the 'Current Agent' using a relatively simple INDEX/MATCH combo.

So the data is structured is well as it can be, but it needs to be able to handle the reporting of information entered by about 15 different people daily. Everything else on this Book can handle this fluidity. It's just this one last hurdle that's causing annoyance.