Text format when cell is empty

Discuss the spreadsheet application

Text format when cell is empty

Postby Q3Man » Sun Apr 13, 2008 5:17 am

I'm trying to add a text format to a cell so when the cell is empty, it displays the word "Empty", and when the user types a value, the value appears. From the documentation, I attempted the following format code:

Code: Select all   Expand viewCollapse view
"Empty"[=]"";@


Which fails for a reason that was not obvious to me.

I was looking at conditional formatting, however, I have several dozen cells which all need different words as a default, and creating a mass amount of styles seemed an incorrect application.

In any event, is there a proper way to do this?
Q3Man
 
Posts: 1
Joined: Sun Apr 13, 2008 5:11 am

Re: Text format when cell is empty

Postby Dave » Sun Apr 13, 2008 3:35 pm

Are you not faced with the dilemma that if a cell contains "Empty", it is not empty? You can control it's content based on the content of another cell, but not in and of itself, I think. That is, you are after a visual appearance. So, you can use two columns, the second reflecting the first with "=if(A1="";"EMPTY";A1) ... . Then simply hide the first for appearance or printing.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Text format when cell is empty

Postby huw » Mon Apr 14, 2008 10:05 am

So far as I can tell, conditional formatting can only apply a style, and I don't see a way to define a style including text which will show when the cell is empty.

Using just Cell value is equal to "" and applying a style that is hatched or has a coloured background can be done.

And yes, a mass of styles will be necessary if you want lots of different types of "empty" indicating.

If you must have text I think helper cells as suggested by David will have to do.
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Text format when cell is empty

Postby kingfisher » Tue Apr 15, 2008 5:08 am

You can use the numbers tab to create a format containing the word 'empty' but the format only applies when you enter something in the cell. I agree that conditional formatting with a style employing the format would be the way to go. I've just tested the formula ISBLANK(cell) and applied a light grey fill through the chosen style: formula is ISBLANK(A1)
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 29 guests