• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to return a blanl cell, rather than a zero in an array formula

Hi guys,

On the attached sheet on the Del note tab, I am trying to achieve a blank cell in column D; instead of a zero.

This way the text from column B can spread across cells b,c,d,e etc.

Any help would be great.

Please ignore all the numbers dotted around, I can hide those with conditional formatting.

Regards

Brian
 

Attachments

  • template home rev 3.xlsx
    145.1 KB · Views: 0
Hi Brian,

The problem is merged cells, which in turn putting 0 as a result of INDEX formula. Either un-merge cells or put INDEX around an IF where the condition should be if =0 than "" or Else INDEX formula. Something like this.

=IF(INDEX('Doors & Accessories'!$F$11:$F$64,SMALL(IF('Doors & Accessories'!$B$11:$B$64<>0,IF('Doors & Accessories'!$B$11:$B$64<>"",-10+ROW('Doors & Accessories'!$B$11:$B$64))),ROW($A1)))=0,"",INDEX('Doors & Accessories'!$F$11:$F$64,SMALL(IF('Doors & Accessories'!$B$11:$B$64<>0,IF('Doors & Accessories'!$B$11:$B$64<>"",-10+ROW('Doors & Accessories'!$B$11:$B$64))),ROW($A1))))


Regards,
 
Hi Brian,

The problem is merged cells, which in turn putting 0 as a result of INDEX formula. Either un-merge cells or put INDEX around an IF where the condition should be if =0 than "" or Else INDEX formula. Something like this.

=IF(INDEX('Doors & Accessories'!$F$11:$F$64,SMALL(IF('Doors & Accessories'!$B$11:$B$64<>0,IF('Doors & Accessories'!$B$11:$B$64<>"",-10+ROW('Doors & Accessories'!$B$11:$B$64))),ROW($A1)))=0,"",INDEX('Doors & Accessories'!$F$11:$F$64,SMALL(IF('Doors & Accessories'!$B$11:$B$64<>0,IF('Doors & Accessories'!$B$11:$B$64<>"",-10+ROW('Doors & Accessories'!$B$11:$B$64))),ROW($A1))))


Regards,

Hi,

Thank you for replying to me.

I'm struggling to understand your answer though.

I've attached the sheet, and removed the merged cells.

On the del. note tab, if you look at rows 48, 49 and 50.

When item quantities are removed from the doors & accessories tab, as the contents of rows 48 - 50 move up the sheet, they do not spread across columns D & E which contains a zero.

Any help you could give would be great

Regards

Brian
 

Attachments

  • template home rev 3.xlsx
    143.8 KB · Views: 0
Hi:

Go to excel options select advanced in the display option for this worksheet
uncheck the option "show zero in cells that have zero values" I guess this is what you are looking for.

Thanks
 

Attachments

  • template home rev 3.xlsx
    145.5 KB · Views: 0
Hi Nebu,

Thanks for that.

For some reason though rows 48 to 52 still do not read across columns B to F, which is what I'm trying to achieve.

Then as rows 48 - 52 move up the sheet as previous rows are not used, they continue to read across columns B to F

Regards

Brian
 
Hi:

I am afraid I understood you correctly, could you manually put the output you are looking for better understanding in a separate tab.

Thanks
 
Hi Nebu,

On the attached sheet you will see rows 48 - 52 highlighted in pink.

As you can see the text doesn't read across all the columns (B to F)

This will also be the case as the text in rows 48 - 52 moves up the sheet.

Regards

Brian
 

Attachments

  • template home rev 3 (5).xlsx
    140.5 KB · Views: 2
Hi:

When I evaluated the formula say the one in the cell E8 it is trying to pull row 39 from the range K11:K64 of tab Doors & Accessories, and it is doing exactly the same and if you cross check this with your Doors & Accessories tab the row 39 for the range K11:K64 (which is essentially cell K49) is blank hence there is no text to read or flow through. Hope this is what you are after.

Thanks
 
Back
Top