Hi,
So I would appreciate some help as my formula doesn't seem to be working, having tried variation of using IF before the INDEX. I'm using the formula below in a different sheet (but the same worksheet) pulling information from sheet 1 (the information below is from sheet 1). I'm using the if statement to filter by Car type ideally (I would like to use two types in the formula there is only!), the second argument in the formula, I need the information in the if statement to either be greater than 0 the "Number of remaining column" or not have the text "No Turns Left" in the "Next turn date column".
=INDEX(Sheet1!G:G,MATCH(MODE.SNGL(IF(AND(Sheet1!A:A="DM",Sheet1!E:E>0),Sheet1!G:G,"")),Sheet1!G:G,0))
So I would appreciate some help as my formula doesn't seem to be working, having tried variation of using IF before the INDEX. I'm using the formula below in a different sheet (but the same worksheet) pulling information from sheet 1 (the information below is from sheet 1). I'm using the if statement to filter by Car type ideally (I would like to use two types in the formula there is only!), the second argument in the formula, I need the information in the if statement to either be greater than 0 the "Number of remaining column" or not have the text "No Turns Left" in the "Next turn date column".
=INDEX(Sheet1!G:G,MATCH(MODE.SNGL(IF(AND(Sheet1!A:A="DM",Sheet1!E:E>0),Sheet1!G:G,"")),Sheet1!G:G,0))
Today: | 13/10/2022 | |||||
Car Type | Last Turn Date | Post-Turn Diameter | Spare Diameter Remaining (mm) | Number of Remaining Turn Cycles | Next Turn Date | |
DM | 31/01/2020 | 716.5 | 11.5 | 0 | NO TURNS LEFT | |
DM | 31/01/2020 | 716.49 | 11.49 | 0 | NO TURNS LEFT | |
DM | 31/01/2020 | 719.49 | 14.49 | 0 | NO TURNS LEFT | |
DM | 31/01/2020 | 718.39 | 13.39 | 0 | NO TURNS LEFT | |
T | 31/01/2020 | 731.97 | 41.97 | 5 | 29/12/2020 | |
T | 31/01/2020 | 732.96 | 42.96 | 5 | 29/12/2020 | |
T | 31/01/2020 | 731.91 | 41.91 | 5 | 29/12/2020 | |
T | 31/01/2020 | 731.96 | 41.96 | 5 | 29/12/2020 | |
UNDM | 31/01/2020 | 725.04 | 20.04 | 2 | 29/12/2020 | |
UNDM | 31/01/2020 | 724.49 | 19.49 | 0 | NO TURNS LEFT | |
UNDM | 31/01/2020 | 724.47 | 19.47 | 0 | NO TURNS LEFT | |
UNDM | 31/01/2020 | 725.49 | 20.49 | 2 | 29/12/2020 | |
ST | 31/01/2020 | 697.93 | 7.93 | 0 | NO TURNS LEFT | |
ST | 31/01/2020 | 697.99 | 7.99 | 0 | NO TURNS LEFT | |
ST | 31/01/2020 | 698.02 | 8.02 | 0 | NO TURNS LEFT | |
ST | 31/01/2020 | 698.09 | 8.09 | 0 | NO TURNS LEFT | |
DM | 15/06/2020 | 757.93 | 52.93 | 5 | 14/05/2021 | |
DM | 15/06/2020 | 754.95 | 49.95 | 5 | 14/05/2021 | |
DM | 15/06/2020 | 757.8 | 52.8 | 5 | 14/05/2021 | |
DM | 15/06/2020 | 758.05 | 53.05 | 5 | 14/05/2021 | |
T | 15/06/2020 | 721.94 | 31.94 | 4 | 14/05/2021 | |
T | 15/06/2020 | 724.93 | 34.93 | 4 | 14/05/2021 | |
T | 15/06/2020 | 722.02 | 32.02 | 4 | 14/05/2021 | |
T | 15/06/2020 | 721.94 | 31.94 | 4 | 14/05/2021 | |
UNDM | 15/06/2020 | 766.18 | 61.18 | 6 | 14/05/2021 | |
UNDM | 15/06/2020 | 764.96 | 59.96 | 6 | 14/05/2021 | |
UNDM | 15/06/2020 | 767.43 | 62.43 | 6 | 14/05/2021 | |
UNDM | 15/06/2020 | 766.02 | 61.02 | 6 | 14/05/2021 | |
DM | 10/07/2019 | 733 | 28 | 0 | NO TURNS LEFT | |
DM | 10/07/2019 | 733 | 28 | 0 | NO TURNS LEFT | |
DM | 10/07/2019 | 736 | 31 | 3 | 07/06/2020 | |
DM | 10/07/2019 | 736 | 31 | 3 | 07/06/2020 | |
T | 10/07/2019 | 736 | 46 | 6 | 07/06/2020 | |
T | 10/07/2019 | 736 | 46 | 6 | 07/06/2020 | |
T | 10/07/2019 | 736.98 | 46.98 | 6 | 07/06/2020 | |
T | 10/07/2019 | 737 | 47 | 6 | 07/06/2020 | |
UNDM | 10/07/2019 | 733 | 28 | 0 | NO TURNS LEFT | |
UNDM | 10/07/2019 | 734 | 29 | 0 | NO TURNS LEFT | |
UNDM | 10/07/2019 | 734 | 29 | 0 | NO TURNS LEFT | |
UNDM | 10/07/2019 | 734 | 29 | 0 | NO TURNS LEFT |