Haunted by the returning zero

[itsgrady]

Currently I am using conditional formatting to turn the Zero white in a cell to make disappear, but it's turning every number with a zero white. Below is my formula for pulling information from 10 different list. Once the information in the cell $AH$6 matching the one of the list it pulls in the information in. If the cell has no information it puts a zero in the cell. I need to remove the zero in cell.

=IFNA(IFS($AH$6="AS",$BB28,$AH$6="BS",$BF28,$AH$6="CS",$BJ28,$AH$6="DS",$BN28,$AH$6="S1",$AI28,$AH$6="S2",$AJ28,$AH$6="S3",$AK28,$AH$6="S4",$AN28,$AH$6="S5",$AO28,$AH$6="S6",$AP28),"")

Here is formula broken down by one
IFS($AH$6="AS",$BB28)

I pull the above long formula down to auto fill the other cells to copy the entire column range.

 

[Fluff]

Maybe

=IFS($AH$6="AS",$BB28,$AH$6="BS",$BF28,$AH$6="CS",$BJ28,$AH$6="DS",$BN28,$AH$6="S1",$AI28,$AH$6="S2",$AJ28,$AH$6="S3",$AK28,$AH$6="S4",$AN28,$AH$6="S5",$AO28,$AH$6="S6",$AP28,1,"")

 

[itsgrady]

Maybe

=IFS($AH$6="AS",$BB28,$AH$6="BS",$BF28,$AH$6="CS",$BJ28,$AH$6="DS",$BN28,$AH$6="S1",$AI28,$AH$6="S2",$AJ28,$AH$6="S3",$AK28,$AH$6="S4",$AN28,$AH$6="S5",$AO28,$AH$6="S6",$AP28,1,"")

The above formula is auto filled down 7 rows. So, when one of the cells is empty it copies over a zero. The new formula you suggest still places a zero in the cell if the cell is it copying is blank.

 

[Fluff]

Which cells are you talking about?

 

[itsgrady]

The above formula returns a zero in the cell that the formula is in (ex: "B2"). If cell BB28 is empty it returns a zero to B2 cell.

 

[Fluff]

In that case what values do you have in those cells, numbers or text.

 

[itsgrady]

General - information like:
6:00AM - 6:00PM

 

[Fluff]

In that case if it's text you can concatenate the cells with "" like

=IFS($AH$6="AS",$BB28&"",$AH$6="BS",$BF28&"",$AH$6="CS",$BJ28,$AH$6="DS",$BN28,$AH$6="S1",$AI28,$AH$6="S2",$AJ28,$AH$6="S3",$AK28,$AH$6="S4",$AN28,$AH$6="S5",$AO28,$AH$6="S6",$AP28,1,"")

 

[itsgrady]

In that case if it's text you can concatenate the cells with "" like

=IFS($AH$6="AS",$BB28&"",$AH$6="BS",$BF28&"",$AH$6="CS",$BJ28,$AH$6="DS",$BN28,$AH$6="S1",$AI28,$AH$6="S2",$AJ28,$AH$6="S3",$AK28,$AH$6="S4",$AN28,$AH$6="S5",$AO28,$AH$6="S6",$AP28,1,"")

No more being haunted by the ZERO. Worked very nicely. Thanks for your help! Now, I wish there was an easy to update all of the formulas. Off to the task of updating the formulas.

 

[Fluff]

Glad to help & thanks for the feedback.