Max Date with Unique Name - Updated Request

Everything is automated.

E5 cell Formula

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

Formula:

=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

Formula:

=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!