How to make the #N/B error invisible?

  • Thread starter Thread starter MightyKitten
  • Start date Start date
M

MightyKitten

Excel 2000 / Dutch

I have a spreadsheet, in witch B2 to AW2 can be filled with "x" or left
blank.
On B1 I'd like to put an "x" if any of those collumns is filled with an "x"
I've used the function

=HORIZ.ZOEKEN("x"; B2:AW2;1;ONWAAR)

It is the Dutch version for =H.Search("x"; B2:AW2; 1 ; False)
And as basic funtion it works. The problem is that if the function does not
find an "x",
it will display "#N/B" in the cell.

The big quiestion is: How do I get rid of it. I'd like the cell toapear
compleetly empty if there aren't any "x"-es put into the columns?

I prefere not to use Visual basic for this, as it is ment to be a quick
dirty trick (and I already have spend way to mucht time on it)

Thanks In Advance,

MightyKitten
 
Hi MightyKitten

Try:
=ALS(NB(HORIZ.ZOEKEN("x"; B2:AW2;1;ONWAAR)),"",HORIZ.ZOEKEN("x";
B2:AW2;1;ONWAAR))

Not tested as I don't have a Dutch version but it's a fairly standard
approach with translations to Dutch equivalent functions.

How's that Ron?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi
you can use the Dutch version of the ISERROR or ISNA function:
=IF(ISNA(your_formula),"",your_formula)
or
=IF(ISERROR(your_formula),"",your_formula)

sorry I don't know the Dutch function names of these functions but in
the function wizard you find them in the information category
HTH
Frank
 
Er... What does NB stand forin the formula? Excel does not onderstand that
part, it seams

And unfortunately for you, I'm not Ron, but Jeroen (never posted here
before)

But thanks anyway for trying...
 
Hi MightyKitten!

Sorry!

Correct response is:
=ALS(ISNB(HORIZ.ZOEKEN("x";
B2:AW2;1;ONWAAR)),"",HORIZ.ZOEKEN("x";B2:AW2;1;ONWAAR))

Ron is a good Dutch friend Ron de Bruin who is a very frequent poster
here when he's not busy translating Function descriptions and Syntax
into Dutch.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks it was almost right ,

=ALS(ISNB(HORIZ.ZOEKEN("x";
B2:AW2;1;ONWAAR));" ";HORIZ.ZOEKEN("x";B2:AW2;1;ONWAAR))

did the trick. I had to repalce the , with ; in the 'Als' statement.
(Another one of those language specific tingies. In my opinion Billyboy
should have sticked with english formula's Macro's and Visualbasic
statements) Just wanted to post the compleet working dutch formula.

You were a great help (and you too, Frank).

MightyKitten
 
Hi MightyKitten!

Good to hear you got it working.

We tend to forget the change in argument separator. Cause me no end of
confusion in Indonesia once when one computer in a class of 15 was
acting differently to all the others.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
To make everything yet somewhat more confusing :
If you program in VBA you have program , in stead of ; in a formula
Cell(I,J).value = "=Sum(A1,A3,A5)" etc.

In the worksheet it shows then : "=Sum(A1;A3;A5)"

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Norman Harker said:
Hi MightyKitten!

Good to hear you got it working.

We tend to forget the change in argument separator. Cause me no end of
confusion in Indonesia once when one computer in a class of 15 was
acting differently to all the others.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Click to expand...


Wish-list item:

It would be very useful if versions of XL other than English (or should that
be American) could be configured to use the English versions of the commands
and separators. This would give consistency with VBA, and make it possible
to share your work with colleagues around the world.

Regards

[snip]
 
Back
Top