Forum Discussion
Last Invoice Timing
mathetes thank you for the response please find the below attachment for the required file, in sheet 1 there are invoice details for each employee and on sheet 2 there is the list of employees where I need the last invoice time.
Hope this sheet will help to resolve me question
I converted your raw data to a Table and shortened the names of the sheets, just because it makes the formula easier to read and create. Having the data as a Table also enables you to enlarge the database and the formula adapts to the new rows automatically.
This formula does use the newly released function FILTER, so you will need to have the most up-to-date version of Excel in order for it to work. Here's the formula
=MAX(FILTER(Table1[Time],(Table1[adduser]=Report!A2)*(Table1[Date]=Report!B2),"NotFound"))
mathetes Thank you, So I need to use this formula in any sheet to get the values am I correct
?
I didn't catch what is the logic of selection name/date in second sheet, but if the purpose is to show max time for each name and each date when PivotTable could work
Layout and design could be adjusted as needed.
You wrote: So I need to use this formula in any sheet to get the values am I correct?
To which I can only answer with a qualified "Yes." That formula, or, more to the point, one like it with updated references, will work where the conditions are essentially the same, but you shouldn't expect to just copy and paste from that sample to another sheet.
Here are two links that can help you understand the FILTER function, which is the heart of what I gave you. The first is an excellent YouTube video which features a Microsoft employee (who occasionally makes an appearance on these pages) introducing the function at a meeting: https://www.youtube.com/watch?v=9I9DtFOVPIg
The other is a website that gives helpful explanations with examples that you can study at your leisure:
https://exceljet.net/excel-functions/excel-filter-function
