Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Don't Display #N/A
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 -- I would give my left hand to be ambidextrous! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Don't Display #N/A
On Sun, 29 Nov 2009 18:30:01 -0800, Dax Arroway
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
some cells display formulas, some display values | Excel Discussion (Misc queries) | |||
How to display remaining txt file which overflowed MsgBox display? | Excel Discussion (Misc queries) | |||
Display every 3rd category name but still display latest month | Charts and Charting in Excel | |||
Can I display an Excel chart as my screensaver display? | Charts and Charting in Excel | |||
Numbers display as decimal, i.e. enter 123 display 1.23 | Setting up and Configuration of Excel |