Remove #N/A N/A

[DianeG]

Hello,


I am using this formula =BDS($B2&"ISIN",$O$1,"Aggregate=n","StartCol=2","StartRow=1","EndRow=1") to retrieve information from Bloomberg. I replicate this formula multiple times in the same column using &"/" to get a list in the same cell. However, when the formula no longer finds a name, it returns #N/A N/A. I would like it to return nothing instead. Could you please help me with this?


Thank you in advance.

 

[Special-K99]

IF(ISNA(formula),"", formula)

So if formula returns #N/A then return blank otherwise execute the formula.

 

[DianeG]

Thank you, but it returns #NAME? Apparently, Excel doesn't recognize my BDS formula, yet it works perfectly fine.

 

[Special-K99]

It might be that BDS returns multi-cell data and ISNA() is expecting a single return value.
I don't use BDS formulas so I can't help any further.

 

[Trixterz]

Use if error statement to return a blank value.

=IFERROR(BDS($B2&"ISIN",$O$1,"Aggregate=n","StartCol=2","StartRow=1","EndRow=1"),"")

 

[Colin Legg]

The #N/A N/A returned by BDS is a string rather than an Excel error type.
I don't have Bloomberg and I'm not sure what the inputs in B2 and O1 are and what the typical output would be so it's difficult to provide a precise answer.

However, as a suggestion you could explore options such as:

1. Checking the inputs to prevent the #N/A N/A happening, for example to check B2:

=IF(B2="","",BDS($B2&"ISIN",$O$1,"Aggregate=n","StartCol=2","StartRow=1","EndRow=1"))

2. Checking the BDS result for "#N/A N/A" and returning an empty string when it is, for example:

=LET(bbgResult,BDS($B2&"ISIN",$O$1,"Aggregate=n","StartCol=2","StartRow=1","EndRow=1"),IF(bbgResult="#N/A N/A","",bbgResult))

 

[DianeG]

The #N/A N/A returned by BDS is a string rather than an Excel error type.
I don't have Bloomberg and I'm not sure what the inputs in B2 and O1 are and what the typical output would be so it's difficult to provide a precise answer.

However, as a suggestion you could explore options such as:

1. Checking the inputs to prevent the #N/A N/A happening, for example to check B2:

=IF(B2="","",BDS($B2&"ISIN",$O$1,"Aggregate=n","StartCol=2","StartRow=1","EndRow=1"))

2. Checking the BDS result for "#N/A N/A" and returning an empty string when it is, for example:

=LET(bbgResult,BDS($B2&"ISIN",$O$1,"Aggregate=n","StartCol=2","StartRow=1","EndRow=1"),IF(bbgResult="#N/A N/A","",bbgResult))

Thanks for your message. The good formula was IF(formula)=« #N/A N/A », « « , formula ). Thanks to all of you !