How to set a default cell value if left blank, otherwise show text in cell?

  • Thread starter Thread starter Toby Erkson
  • Start date Start date
T

Toby Erkson

I have a lookup table named "LOOK". There are some fields in it that are empty (I know, not good, I tell my end users that!). I want to set blank (empty)
cells with a value, say, "_Error!". I'm drawing a complete Monday-brain blank. How do I set the cells to display the default "_Error" string (or whatever
value I want) if the end user did not enter a value in the cell.

When the cell is empty, my VLOOKUP function returns the error "#N/A" and that, of course, causes even more problems.
Thanks,
Toby Erkson
Oregon, USA
 
Hi Toby
There's no such format to achieve this error message. Best way I could
think of would be to create a macro which runs afterwards and highlight
the cells.
Some other ideas:
1. Use conditional formatings:
- select your range (lets say you start in A1)
- enter the formula
=ISBLANK(A1)
- choose a format for this

2. Prevent the #NA error message. You may change your current VLOOKUP
formula to
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

--
Regards
Frank Kabel
Frankfurt, Germany

I have a lookup table named "LOOK". There are some fields in it that
Click to expand...
are empty (I know, not good, I tell my end users that!). I want to set
blank (empty)
cells with a value, say, "_Error!". I'm drawing a complete
Click to expand...
Monday-brain blank. How do I set the cells to display the default
"_Error" string (or whatever
value I want) if the end user did not enter a value in the cell.

When the cell is empty, my VLOOKUP function returns the error "#N/A"
Click to expand...
and that, of course, causes even more problems.
 
Thanks Frank. Actually, your #2 is another question I posted because that doesn't work. I'll play around with your #1 suggestion.
Toby

Hi Toby
There's no such format to achieve this error message. Best way I could
think of would be to create a macro which runs afterwards and highlight
the cells.
Some other ideas:
1. Use conditional formatings:
- select your range (lets say you start in A1)
- enter the formula
=ISBLANK(A1)
- choose a format for this

2. Prevent the #NA error message. You may change your current VLOOKUP
formula to
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
Click to expand...

Toby Erkson
Oregon, USA
 
Back
Top