Hello again All,
Its been a while but once I again I require some help in solving a tricky little excel problem before the festive period begins.
Workbook attached is to do with staff training at work and monitoring what courses have been completed by staff.
Worksheet "Training Activity" is a list of raw data to show all training courses being taken by staff, as time progresses this list will not follow any set order as data can be added at any time and dates will not be in order, this sheet contains some basic drop down lists to bring in data from the "Data List" worksheet but that is all.
The main worksheet I am looking to improve is "Financial Year" at the moment the table uses an array formula I have been working on for some time which contains INDEX, SMALL, IF and ISERROR calculations.
{=IF(ISERROR(INDEX('Training activity'!$A$4:$M$16,SMALL(IF('Training activity'!$A$4:$A$16=$C$8,ROW('Training activity'!$A$4:$A$16)),ROW(1:1))-3,2)),"",INDEX('Training activity'!$A$4:$M$16,SMALL(IF('Training activity'!$A$4:$A$16=$C$8,ROW('Training activity'!$A$4:$A$16)),ROW(1:1))-3,2))}
Basically it will scan the table on the "Training Activity" worksheet and bring in any data that matches the users Name shown in cell C8 with the relevant column in the data. It has been built in a way to ensure it shows only unique rows and does not display any lines that contain errors. This was a giant step forwards for me in going away from a standard "LOOKUP" formula.
My next challenge is to now filter results by dates. I only want data to be shown that falls between the Start Date (cell F8) and End Date (cell F10). Of course the additional formula will need to be inserted into every cell that has a formula as each cell is independent and I am now banging my head against the desk.
Any help will be greatly appreciated.
Bookmarks