Don't Display #N/A

[Dax Arroway]

I have a formula that pulls info from another page. The formula is:

{=INDEX('Patient Count'!$J2:$J5000,MATCH(1,('Patient
Count'!$C$2:$C$5000=A2)*('Patient Count'!$N$2:$N$5000=""),0))}

which, if there's no information entered in the indicated cell of the
referring sheet, a #N/A is displayed. I'd like to the cell to remain blank.

What is the code I should add to the above formulat to NOT display the #N/A
in the cell?

Thank you very much in advance!
Dax

 

[Ron Rosenfeld]

I have a formula that pulls info from another page. The formula is:

{=INDEX('Patient Count'!$J2:$J5000,MATCH(1,('Patient
Count'!$C$2:$C$5000=A2)*('Patient Count'!$N$2:$N$5000=""),0))}

which, if there's no information entered in the indicated cell of the
referring sheet, a #N/A is displayed. I'd like to the cell to remain blank.

What is the code I should add to the above formulat to NOT display the #N/A
in the cell?

Thank you very much in advance!
Dax

You write that you don't want the cell to *DISPLAY* #N/A. Do you also mean
that you don't want the cell to *CONTAIN* #N/A?

You can still have NA in the cell but make the contents invisible by using
conditional formatting to change the cell font color to the same as the
background color if the cell contains #N/A. This might be handy if you are
graphing the results.

If you want to eliminate the N/A completely, then something like:

=if(isna(INDEX('Patient Count'!$J2:$J5000,MATCH(1,('Patient
Count'!$C$2:$C$5000=A2)*('Patient
Count'!$N$2:$N$5000=""),0))),"",INDEX('Patient
Count'!$J2:$J5000,MATCH(1,('Patient
Count'!$C$2:$C$5000=A2)*('Patient Count'!$N$2:$N$5000=""),0)))

If this will be run in Excel 2007+, you could use the shorter:

=iferror(INDEX('Patient Count'!$J2:$J5000,MATCH(1,('Patient
Count'!$C$2:$C$5000=A2)*('Patient Count'!$N$2:$N$5000=""),0)),"")

--ron