Max Date with Unique Name - Updated Request

[spider1123@yahoo.com]

"mike7952" and "alansidman" had provided an answer to my initial post regarding this but (good news/bad news) - they liked it so much, they said, "wow ... hey, then can you figure out how to do this ... ?" So I sat them down and narrowed down precisely (in their best world scenario) what they wanted. So here goes ... see the attached.

We want to have a "summary worksheet" that will summarize for each of the worksheets which have the detail of each prospect by marketing partner.

Please let me know if you have any questions. I was struggling with my initial request so now this one is way beyond me so I appreciate your help!

Regards,
Tom

[spider1123@yahoo.com]

As an update ... if someone can identify a function (or likely a series of functions) that will allow me to determine the latest date (Column B) for each prospect (Column C), I can figure out the rest.

Thanks for your help.

[:) Sixthsense :)]

Everything is automated.

E5 cell Formula

=IF(D5="","",SUMPRODUCT(MAX(INDIRECT("'"&B5&"'!B5:B100")*(INDIRECT("'"&B5&"'!C5:C100")=D5))))

Drag the formula down

F5 cell Formula - Array Formula - Requires Ctrl+Shift+Enter

=IF(D5="","",INDEX(INDIRECT("'"&B5&"'!E5:E100"),MATCH(1,(INDIRECT("'"&B5&"'!B5:B100")=$E5)*(INDIRECT("'"&B5&"'!C5:C100")=$D5),0)))

Drag the formula down

G5 cell Formula - Array Formula - Requires Ctrl+Shift+Enter

=IF(D5="","",INDEX(INDIRECT("'"&B5&"'!G5:G100"),MATCH(1,(INDIRECT("'"&B5&"'!B5:B100")=$E5)*(INDIRECT("'"&B5&"'!C5:C100")=$D5),0)))

Drag the formula down

Please keep in mind that each column formula's are different, So drag the formula down for that columns alone.

Sheet names will be automatically picked and located to the concerned sheet based on the Name You enter in Column C.

I am using Column B as helper column, since i require the sheet names needs to be carried over to the next rows to make the formula to pick the sheet automatically. At the same time i dont want to affect your layout so using column B as helper column.

You have to fill only the Column-C and Column-D and dont forget to fill the Column-B no other changes are required. Just drag the formula below it will take care of the remaining calculations.

Note: In the above formula's at present I have defined the range as Row 5 to Row 100 if suppose your data of ANY OF THE SHEET crosses Row 100 the just change the range reference 100 to your desired Rows count.

Refer the attached file for details.

Hope that helps!

[spider1123@yahoo.com]

Hey, Sixthsense, thanks for your help. Almost there ... is there a way to have the partner and prospect names filled without having to type them in? That is, have it process on worksheet at a time.

Like ... get the first worksheet and select its name, then find the unique Prospect name, then the latest date for that prospect name (and associated information); then get the next unique Prospect name and find the latest date for the prospect name (and associated information); and so on. Then go on to the next spreadsheet.

Hmmm ... almost sounds like it needs to be macro code but I was hopeing to just use formulas as I'm not a "macro" guy. I will continue to fiddle with it based on what you got me so if you can help further, that's great.

Thanks for your help so far!

Best,
Tom

[spider1123@yahoo.com]

Ok ... Sixthsense, I took what you gave me and with a fair amount of searching and guessing, I got it to work. I'm attaching just in case someone else ever runs into the same problem. Thanks for your help!!

Cheers,
Tom

[:) Sixthsense :)]

Ok ... Sixthsense, I took what you gave me and with a fair amount of searching and guessing, I got it to work. I'm attaching just in case someone else ever runs into the same problem. Thanks for your help!! Tom

If you are comfortable with the formula you arrived, then proceed with it... But from my point of view Ms-Access will be the good choice for your data management.