referencing the cell which returns empty cell in its formula

0
votes

I have the following formula which I calculate the minimum value in Sheet2 in a dynamic range

 =IF(MIN(I21:INDEX(I:I;MIN(IF(A21:A108="";ROW(A21:A108)))))=0;"";MIN(I21:INDEX(I:I;MIN(IF(A21:A108="";ROW(A21:A108))))))

and I have following formula which returns the cell I20 of Sheet2(which my above formula is inside)

 =WEEKNUM(Sheet2!I20)

When I20 does not return any value my Weeknum formula returns #VALUE! and i want it to return only if there is a value in I20 and if there is no value in I20 then i want it to return empty.

How can i do this?

enter image description here

enter image description here

2
excelexcel-formula

2 Answers

2
votes

Use:

=IFERROR(WEEKNUM(Sheet2!I20),"")

instead.

1
votes

To avoid the error, use IFERROR

=IFERROR(IF(MIN(I21:INDEX(I:I;MIN(IF(A21:A108="";ROW(A21:A108)))))=0;"";MIN(I21:INDEX(I:I;MIN(IF(A21:A108="";ROW(A21:A108)))))),"")