Good day I have the following formulas in my excel sheet:
H6=> =VLOOKUP($E6,'3 Nov 21'!$B:$V,14,)
I6=> =IF(H6="","",(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24)
J6=> =VLOOKUP($E6,'3 Nov 21'!$B:$V,20,)
K6=> =IF(J6="","",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24)
L6=> =ROUNDDOWN(((H$2-$F6)/365.25),0)
M6=> =IF(AND(H6=0,J6=0),0,IF(H6="",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24,(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24))
N6=> =MATCH(M6,IF(H6=0,('8km Points'!$K$4:$K$234),('5km Points'!$K$4:$K$185)),1)+3
O6=> =HLOOKUP(L6,(IF(H6=0,'8km Points'!$M$3:$V$234,'5km Points'!$M$3:$V$185)),(N6-2))
My problem is that as soon as I have 0.00.00 in column 14 of sheet 3 Nov 21 it creates a #value error in cells I6 which in return creates the #value error in these cells as well: M6;N6;O6.
How can I fix I6 to reflect it to be blank if it encounters 0.00.00?
At the same time the same principle can be used for H6 and J6
H6=> =VLOOKUP($E6,'3 Nov 21'!$B:$V,14,)
I6=> =IF(H6="","",(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24)
J6=> =VLOOKUP($E6,'3 Nov 21'!$B:$V,20,)
K6=> =IF(J6="","",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24)
L6=> =ROUNDDOWN(((H$2-$F6)/365.25),0)
M6=> =IF(AND(H6=0,J6=0),0,IF(H6="",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24,(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24))
N6=> =MATCH(M6,IF(H6=0,('8km Points'!$K$4:$K$234),('5km Points'!$K$4:$K$185)),1)+3
O6=> =HLOOKUP(L6,(IF(H6=0,'8km Points'!$M$3:$V$234,'5km Points'!$M$3:$V$185)),(N6-2))
My problem is that as soon as I have 0.00.00 in column 14 of sheet 3 Nov 21 it creates a #value error in cells I6 which in return creates the #value error in these cells as well: M6;N6;O6.
How can I fix I6 to reflect it to be blank if it encounters 0.00.00?
At the same time the same principle can be used for H6 and J6