Indirect Formula

[alibbye17]

Good Morning All,

Im using the following indirect formula within my spreadsheet:

=IF(SUM(INDIRECT($D66&"!ad22")=0),"-",SUM(INDIRECT($D66&"!ad22")))

Instead of having a value been shown as 0, I have the formula setup to show "-" (this is easier on the eyes). However, i just realized that anytime the value is below 0 the formula still show "-". This isnt correct and I cant seem to figure why. Can anyone help out....

 

[Peter_SSs]

Have you applied a custom number format? If so, what format did you use?

 

[alibbye17]

I did...

_($* #,##0,_);_($* "-"_);_(@_)

As i can see, it doesnt pick up negatives. Thanks!

 

[Aladin Akyurek]

Good Morning All,

Im using the following indirect formula within my spreadsheet:



Instead of having a value been shown as 0, I have the formula setup to show "-" (this is easier on the eyes). However, i just realized that anytime the value is below 0 the formula still show "-". This isnt correct and I cant seem to figure why. Can anyone help out....

Why not just invoke:

=SUM(INDIRECT($D66&"!ad22"))

and custom format the formula cell, say, as:

[=0]"-";General

 

[alibbye17]

Makes sense.

However, instead of using General I need to set up the format in Thousands.

Thanks

 

[Aladin Akyurek]

Makes sense.

However, instead of using General I need to set up the format in Thousands.

Thanks

You should be able to replace General with a more convenient format string like in:

[=0]"";#,##0.00