ignoring blank cells in an array forumla

[ENAPo]

I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))

If the cell it's pulling the information from is blank it puts a 0 in the cell. I want it to just leave it blank if the cell is blank but am at a loss where to put that in the formula. Hope that doesn't sound confusing.

Thanks!!

 

[Ron Coderre]

Are the expected values numeric, text, or either?

 

[ENAPo]

They are classified as General. One column has text and numbers and another column is primarily just text.

 

[Aladin Akyurek]

In which cell did you enter this formula?

 

[ENAPo]

its across a whole spreadsheet. I am pulling information from another worksheet that is imported from QB based on information from another cell. The rows change as does the column number. I cannot do a snapshot unless I change all the information.

 

[Aladin Akyurek]

its across a whole spreadsheet. I am pulling information from another worksheet that is imported from QB based on information from another cell. The rows change as does the column number. I cannot do a snapshot unless I change all the information.

The formula as witten does not indicate any change in the column number for it's hard-coded and set to 5. However, what I was asking for is the first cell which houses this formula.

 

[Hercules1946]

Hi,
I'll probably get slated for over-simplifying or missing something but if you just want to surpress zero then why not do so in the Advanced options under Display (whole workbook)
or:

Select individual cells and create a custom format for numbers for them:

0;-0;;@

HTH

 

[Hercules1946]

If you are able to control the source data, and you dont like my formatting suggestions, you could change the "" state of the cells by inputting eg a space character. I agree with you that the appearance of zero values for no logical reason can make a mess of your data and there should be a better solution.

On the issue of which cell the formula is first input to, it works in any cell, because of the way the $ symbol has been used. Placed in any cell unchanged however, there would need to be 5 instances of the search criteria in the source data table for it to return a value.

 

[ENAPo]

The advanced options works great. I didn't even know that option worked. Thanks, Hercules!

 

[Aladin Akyurek]

The advanced options works great. I didn't even know that option worked. Thanks, Hercules!

Your formula calculates the same thing twice and it is not robust...