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

[brianaclark1967]

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

 

[Somendra Misra]

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,

 

[srinidhi]

HI,

Do an CF, highlight cell rules>equal to>0>customFormat>font, white

 

[bobhc]

@srinidhi

CF will not work with merged cells

 

[brianaclark1967]

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

 

[Nebu]

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

 

[brianaclark1967]

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

 

[Nebu]

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

 

[brianaclark1967]

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

 

[Nebu]

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