Ignoring Blank Cells In An If Statement Formula

Hi there,

I have a spreadsheet with some cells setup with a drop-down list containing
Y, N or N/A

This is being used on a TabletPC but if I make a mistake or need to change
back to a blank field I have to invoke the soft keyboard, activate the cell
and hit backspace then close the soft keyboard - quite a long-winded
procedure just to change an incorrect choice!

What I would like to do is add a blank to the list so if I have to revert
back to a blank I can just use the stylus to choose a blank from the chooser
list.

How do I add the option of inputting a blank from the Data Validation List
bearing in mind I am using the Data Validation Source box for entering my
choices directly and not specifying a range of cells?

I have tried adding "" and even a space to no avail.

Although not a betting man I would wager there is a simple 'fix' for this
but things are only simple if you know how in the first instance! ;^)

--
Thanks & regards,
-pp-

Hi!

I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.

However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.

I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?

Any advice is higly appreciated.

I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.

Help

I am trying to write a formula that uses both an if statement and a vlookup statement.

It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.

So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")

But it doesn't work. Is it possible to nest a vlookup in an if statement?

is it possible to put two formulasinto one cell?

On the attached timesheet there are columns IN, OUT, IN,OUT
The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula
=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00
Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.
The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success
Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)

I often use if statements to return empty cells, for example:

=IF(a1=0,"ERROR","")

The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?

Thanks

Is it possible to change the color of text using a formula, such as an IF
statement? What I have in mind is something like this (stated simply): If
cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4
red.

Hi, all - I have been banging my head on the desk over this one...it's gotta be so simple, right? Have searched online, and on Mr. Excel, but still can't get this right.

I need a formula in cell L3 that says "IF cell K3 is greater than 25 but less than 30, K3, otherwise, blank." Cell K3 currently contains the value 30. My formula in L3 currently is =IF(K3>25,K3,IF(K3

I have a formula in cell C1 (=A1/B1). I want to copy this formula down to row C20, however rows A2 to A20 are empty so C2 to C20 show (#DIVO!). Is there something I can put in the formula to make column C be blank until the data is enter into Columns A & B?

In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.

I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work

I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.


any Ideas???

Thanks

Here's my formula... =SUM(S7)-T5

If that number is less than -100, I want it to show as blank.

Any help?

Right now, I'm trying to accomplish this by making a conditional format...meaning when my cell equals less than -100 I make the cell color and font the same (so it looks blank, but its not). When I print it, it still shows the negative value.


Thanks.

Could you guide me please....

I need to insert 5 blank rows repeatedly between every existing data rows
for approximately 300 rows.

If I go about doing the repeat short-cut "Control+Y", it just repeats
inserting ONE row only between the consecutive data row.

Is there some command, which helps me highlight all the rows & allows me to
insert 5 blank rows between every consecutive existing data row?

Thanks in advance for your kind advice.

Hi,

How would I concatenate a large range of cells, most of which are blank, and add a comma between each cell's value?
My range is E2:Y2 (I will be filling down), but most of the cells are blank. For example, if I F2=9, I2=11 and X=25, I would like to display them as: 9,11,25.
If in the next row, G=10, K=15 and Y=27, they should look like this: 10,15,27.

Thanks in advance!

Jenny

From limited experience I know that excel calculates dates via serial numbers.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.

Hello All, I have a column where cells need to be 50 characters long, it contains addresses, however, since all the addresses do not meat the criteria, I need to add (blank spaces) to reach the 50 characters, any idea on how to approach this?

Example

Fire Street #50 this cell contains 17 characters therefore I need to add the rest by adding blank spaces to reach 50 characters, otherwise the system I am exporting the data will not understand this cell