[Solved] Test for blank cell and put word into cell if blank

Discuss the spreadsheet application
Post Reply
Hasaf
Posts: 3
Joined: Sun Nov 02, 2008 7:50 am

[Solved] Test for blank cell and put word into cell if blank

Post by Hasaf »

How do I Test for a blank cell and put a word into the cell if it is blank?

I have an existing spreadsheet with about 200 rows and 40 columns. This is a class grade book. There are several blanks and I want to put the word "absent" into the blank cells.

I tried =IF(ISNUMBER(Q67);Q67;"absent") it did not work. when pasted over a set of test numbers (no, I am not testing with a live dataset) it replaced the existing numbers, and, of course, put in the word "absent."

This needs to be applicable to an existing dataset.

Yes, I know I can go through and type the word, "absent," into all of the blank spaces. I am looking for something a little more elegant. I did try search and the extended help referenced to at the top of the page.


Thank You
Last edited by Hasaf on Tue Nov 04, 2008 2:16 pm, edited 1 time in total.
OOo 3.0.X on Ms Windows XP + Linux
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: How do I Test for blank cell and put word into cell if blank

Post by squenson »

Try: =IF(Q67="";"Absent";Q67)
The empty string is two double quotes without anything in between.
LibreOffice 4.2.3.3. on Ubuntu 14.04
Hasaf
Posts: 3
Joined: Sun Nov 02, 2008 7:50 am

Re: How do I Test for blank cell and put word into cell if blank

Post by Hasaf »

Nope, it pasted "absent" over the existing data.

I am thinking that the formula needs to look at a remote cell (not itself) and run a check, then increment by one and do it again. How to do that, I have no idea (hey, I teach English).
OOo 3.0.X on Ms Windows XP + Linux
User avatar
bobban
Volunteer
Posts: 535
Joined: Sat Nov 01, 2008 3:12 pm
Location: Australia

Re: How do I Test for blank cell and put word into cell if blank

Post by bobban »

I believe there is really no entirely elegant way to do it without a macro. Search and Replace does not find empty cells apparently, and every other solution is a hacky workaround. I will write this for you if you want(just send me a cut down sample file through PM).
OOo 3.1.1 on Ms Windows XP
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: How do I Test for blank cell and put word into cell if blank

Post by squenson »

I did not realize that you try applying the formula to cell Q67 itself! What you should do is create it in a new column (for example column AB): put in AB1 =IF(Q1="";"Absent";Q1), copy the formula down the rows. Then select all the cells in that column, do a copy and then a "Paste Special" onto cell Q1 with Values.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
bobban
Volunteer
Posts: 535
Joined: Sat Nov 01, 2008 3:12 pm
Location: Australia

Re: How do I Test for blank cell and put word into cell if blank

Post by bobban »

Make a macro from the following code and bind it to a key for convenience. This lets you select a range of cells, and if they are empty then the word "Absent" is put in them.

Code: Select all

sub FillEmptyCells

	oSelection = ThisComponent.getCurrentSelection()
	oRangeAddress = oSelection.getRangeAddress()

	for iRow = 0 to (oRangeAddress.EndRow-oRangeAddress.StartRow)
		for iCol = 0 to (oRangeAddress.EndColumn-oRangeAddress.StartColumn)
			oCellToCheck = oSelection.getCellByPosition(iCol,iRow)
			if( oCellToCheck.Formula = "" ) then
				oCellToCheck.Formula = "Absent" 
			end if
		next iCol
	next iRow

end sub
OOo 3.1.1 on Ms Windows XP
Hasaf
Posts: 3
Joined: Sun Nov 02, 2008 7:50 am

Re: How do I Test for blank cell and put word into cell if blank

Post by Hasaf »

Thanks, that is great. It is odd that is misses one cell (C195). There is probably some invisible data in that cell. It is great. I knew that there had to be a more elegant way of doing it than the manual way.

I will give that macro to the other teachers that are using OO for their classes. It just looks better than having blanks in the grade book (the students have been told that the homework is how roll is taken and that if they do not have their homework then they are, by definition, absent.
OOo 3.0.X on Ms Windows XP + Linux
mooncalf
Posts: 1
Joined: Mon Nov 03, 2008 5:56 pm

Re: How do I Test for blank cell and put word into cell if blank

Post by mooncalf »

Thanks very much too, Bobban (and Hasaf for asking the question). You've just helped me a lot

I'd created a document in the pre-release version of Openoffice.org 3.0 - from some data sent to me in an excel file- then found when I installed the shiny new 3.0, lots of my calculations were invalid as blank cells were no longer being read as having a value of zero (at least, that's what I think happened). Anyway, there were lots of calculations based on this data , and there was a lot of data - that's why I was using 3.0 in the first place - for all its lovely extra columns. And it was all spoiled.

It seemed that as I can't find and replace blank cells, a macro was my only option, but I know nothing about them. Luckily I found this thread, changed "absent" in your code to "0", and it worked. I can't tell you how happy it made me to watch the macro work its way through 96,000 cells, and to see all the #VALUE!s disappear.

So you've saved me a lot of trouble, and also helped me make my first foray into the murky world of macros. Cheers!
Last edited by mooncalf on Mon Nov 03, 2008 6:30 pm, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How do I Test for blank cell and put word into cell if blank

Post by Villeroy »

Indeed, FInd&Replace can not find blanks. With the macro you lose the information that the cell has been blank. I'd prefer a simple formula like: =IF(ISBLANK(A1);"missing";A1) Finally you may decide to drop the initial information and paste-special the formula cells over the original cells (values only, leaving out the formulas).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply