SOLVED

Referencing a formula

Copper Contributor

I have a cell with this formula in The sheet 'FDS VERSO' AY9 "=MIN('FDS RECTO'!P59:P82)"

 

Theses referenced cells ('FDS RECTO'!P59:P82) contain this formula:

for the cell  P59:

=MIN(Q59:S59), The refered cells are "=another cell"

for the cell P60

=MIN(Q60:S60)

and so on. I know it can look complicated for nothing but its easier for my job. 

 

All of those cells have the same format but the cell that contain "=MIN('FDS RECTO'!P59:P82)" does not show anything.

 

Is there a way to make it work? FILE INCLUDED 

 

Thanks!

4 Replies
best response confirmed by alex2476 (Copper Contributor)
Solution

@alex2476 

This is because some of the rows in 'FDS RECTO'!Q59:S82 contain only zeros, and hence 'FDS RECTO'!P59:P82 contains zeros.

This is in turn, because some of the rows on the CCM sheet are empty. For example, CCM!D15 is empty, and hence the formula =CCM!D15 returns 0. This is a quirk of Excel.

One workaround is to change the formula in 'FDS RECTO'!P59 to

=IF(MIN(Q59:S59)=0,"",MIN(Q59:S59))

and fill it down to P82.

@alex2476 

show a zero in cells that have zero value.JPG

In Excel options you can select advanced ("Erweitert" in german) and then tick the selection "show a zero in cells that have zero value".

So simple did not think of it since my cells were blank. Thanks!
Yes it makes it more obvious thank you!
1 best response

Accepted Solutions
best response confirmed by alex2476 (Copper Contributor)
Solution

@alex2476 

This is because some of the rows in 'FDS RECTO'!Q59:S82 contain only zeros, and hence 'FDS RECTO'!P59:P82 contains zeros.

This is in turn, because some of the rows on the CCM sheet are empty. For example, CCM!D15 is empty, and hence the formula =CCM!D15 returns 0. This is a quirk of Excel.

One workaround is to change the formula in 'FDS RECTO'!P59 to

=IF(MIN(Q59:S59)=0,"",MIN(Q59:S59))

and fill it down to P82.

View solution in original post