Making Contents Of Cell Blank Upon IF Statement

Feb 10, 2008

I have a conditional IF statement, where I want the content of the cell to be blank if the result of the IF statement is false, ie I want the content of the cell to be as if there were no formula in the cell (this if so that the formula COUNTA(Sheet1!$B:$B) only counts the cells where the result is TRUE).

For example, IF(A1=1,1,"") where A1 1, the content of the cell will have something in it (a space), and won't be blank.

View 9 Replies


Excel 2010 :: Making Chart Title From Cell Contents?

Jan 10, 2014

I am trying to make the contents of a cell into a chart title.I know I can do it like this...

Selection.Caption = "=tab_name!R2C1"

However, I am trying to use the same macro on several different tabs in the same EXCEL worksheet. Obviously, I can't have the tab name in the macro. But, I don't get a title at all when I try to remove it like this...

Selection.Caption = "=R2C1"
Selection.Caption = "=A2"

Is this possible? I am (kinda new at) using EXCEL2010.

View 3 Replies View Related

Making A Cell Blank

Feb 19, 2007

I have a spreadsheet with about 1000 rows. This is data that I've imported from another source and there are blank rows scattered throughout. I am trying to add columns together, but when I cut and past the sum formula, I end up with a 0 in a cell that should be blank. I've tried various iserror, isblank, isnull, empty functions, but am still getting a 0.

Here my example of one function I've tried:
a b c
1. 50 50 50 =if(isblank(a1:c1)," ",sum(a1:c1)) 150
2. 20 20 20 =if(isblank(a2:c2)," ",sum(a2:c2)) 60
3. =if(isblank(a3:c3)," ",sum(a3:c3)) 0

I would like that zero answer in row 3 to be blank otherwise I have to manually go through all the rows and delete these 0's out or cut and paste the sum function just on the rows with numbers.

View 9 Replies View Related

Making A Cell Blank Referring To Another Blank Cell?

May 8, 2014

Im currently writing a payroll sheet within excell and trying to write an IF statement to make a cell blank if referring to another blank cell but it is showing the formula is incorrect. Below is the following IF statement that im using;

=IF( F4 <97, AM4*0.8, IF( F4 >=97, AM4*1.1, IF( F4=" ";" "; AM4)))

View 5 Replies View Related

IF Statement To Leave Cell Blank If Multiple Cells Are All Blank?

Mar 12, 2014

I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.

View 8 Replies View Related

IF Statement Formula Which Will Change The Contents Of The Cell

Jan 23, 2008

I need to create a formula which will change the contents of the cell to one of 3 options based on the value of another cell

For example if I was using B2 as the cell to reference I need to say IF B2 = 0 then say "Closed", IF B2 = 1 then say "Open" and if its any other value then say "Error".

I'm having some trouble having all 3 if statements nested correctly.

I don't want to do this as a macro just as a formula.

View 9 Replies View Related

If Statement To Check Part Of Cell Contents

Apr 4, 2007

IF statement that looks at a cell to see if it contains a word.

ie IF A2 has " Total" in it then do this

View 9 Replies View Related

Change Cell Contents With Conditional Statement

May 30, 2008

i am trying to automate altering a DAT file. Basically, each line of data in the DAT file contains an entity name, an account name, and a value. for some of the accounts, i need to change the entity name. for all other accounts, i can leave the entity name alone. if i copy the DAT file into Excel, and use text to columns, how can i create a macro to compare the accounts to a range of cells containing a list of the accounts i need to alter the entity name for, and then change the entity name if there is a match?

the account does not need to match perfectly, the accounts i input only needs to contain the reference account. the list of accounts and the entity name i need to change to are in cells on another sheet. i thought about doing a loop to run through each line of data, but i am not sure how to do that.

View 2 Replies View Related

If Statement - Check Contents Of Cell To See If It Equals Date

Oct 19, 2011

I am trying and failing to write an if statement in excel (not vba) to check the contents of cell H3069 to see if it equals 29/06/2011.

I have written the following
which returns #Value!

View 4 Replies View Related

How Do I Select & Use Contents Of First Non-blank Cell In Row

Nov 9, 2005

I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

View 9 Replies View Related

Formula On Making A Statement

Jan 20, 2014

I need a formula to look at a cell and if it is =< 102 say "Flat Bed", or =120 "Double Drop"

View 1 Replies View Related

Blank Out Certain Cells Depending On Cell Contents?

Oct 21, 2013

I have a spreadsheet that can do more than one calculation on each row



The values in row A can only be selected from a named range drop down. If no entry is there (i.e. blank) then i'd ideally like the cells to return from black to white.

View 9 Replies View Related

How To Return Contents Of Last Non-blank Cell In Column

May 7, 2014

I am trying to figure out how to return the contents of last non blank cell in column B, based on the name in column A. So, if I choose "Sam", the result I am looking for is "blue". If I choose "Pete", the result I am looking for is "orange".


View 4 Replies View Related

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

Jun 1, 2014

i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows

View 3 Replies View Related

How To Check Activecell If Blank Then Copy Contents From Cell Above

Apr 16, 2012

with some basic code. I want to check the activecell, if it's blank then copy the contents from the cell above, if it's not blank, leave it & go down to the next cell & repeat until last row.

View 7 Replies View Related

If Statement To Return A Blank Cell?

Apr 17, 2014

I can't seem to get this If statement to return an empty cell It returns 0

HTML Code: 

=IF(+B7+C7="","", +$C$3-B7+C7)

The formula will go in D7
If either B7 or C7 is empty, leave D7 empty
If either B7 or C7 has a value, add or subtract from $C$3

View 6 Replies View Related

IF Then Statement To Test If A Cell Is Blank

Aug 19, 2009

How would I write an IF Then Statement to test if a cell is blank? (meaning it could have "Div/0!", text, 0, or "n/a".)

View 4 Replies View Related

If/Then Statement To Place A 0 In A Blank Cell

Jun 8, 2007

If a cell is blank, what is the formula that can be used to place a 0 in that spot if the spot is empty. Also, what is the formula to leave a spot blank if no data can be pulled for its formula (to get rid of a #DIV/0! error).

View 9 Replies View Related

Automatically Put Quotes Around Contents Of Every Cell Including Blank Cells?

Feb 13, 2014

I'm migrating data from Financisto for Android to CashTrails for iOS, and I need to format my CSV file a specific way for conversion to a CashTrails file.

I'd like to do the following in Excel 2013 (I have access to other versions if I need to):

1. Highlight a range of cells (i.e. A1-H20)

2. Apply following action: IF cell has data, add " to beginning of cell and " to end of cell, AND IF cell has no data, place "" in cell.

This can be done, right? I'm desperate!

It would look like this in Notepad.
12/31/13,12:00 PM,$3.99,General,,Checking
12/31/13,12:30 PM,$5.00,Shopping,gift,Cash

"12/31/13","12:00 PM","$34.99","General","","Checking"
"12/31/13","12:30 PM","$5.00","Shopping","gift","Cash"

View 2 Replies View Related

If Statement To Return Blank If Cell D1 Is Empty...

Nov 11, 2008

I have a problem with finding the right function. My goal is to have the following function...

The cell D1 has the following function: [=IF(B1<=C1;"YES";"NO")] But I would like to have the cell D1 to be left blank, if the cell A1 is blank.

View 8 Replies View Related

Copy A Cell Contents Until A Blank Cell

Mar 17, 2008

I would like a macro that copys the contents of a cell and pastes it into the column before it and continues to copy that same number downward as long as the cell next to it contains numbers. So: ....

View 9 Replies View Related

Clear Contents Of A Cell Of Row A, If Cell In Row I Is Blank

May 6, 2009

I need to clear the contents of the cell in Row "A", if the cell in Row "I" is blank.

The issue, is there are roughly 1200 excel documents in a folder and subfolders. That is a painful amount of opening workbooks, sort ascending and deleting. Not on my top 10 list of things to do.

If someone could create a code to do this, and loop it through a folder (I can move all the files to a single folder manually, not an issue)

View 6 Replies View Related

Show Contents Of Cell If Cell Next To It Is Not Blank?

Jun 28, 2012

I have a column of dates in column E1:E100.
E1 = 25/06/2012
E2 = 02/07/2012
E3 = 09/07/2012

In column F1:F100 I will insert "Y" in the one of the blank cells next to one of the date cells in column E1:E100.

In cell B1 I want to reference where the cell in column F is "Y" then use respective date in column E.


If i have "Y" in cell F1 then in B1 I want to show E1 (25/06/2012).
If i have "Y" in cell F2 then in B1 I want to show E2 (02/07/2012).
If i have "Y" in cell F3 then in B1 I want to show E3 (09/07/2012).

View 3 Replies View Related

Summing Cells That Contain IF Statement To Leave Cell Blank If Zero

Mar 14, 2014

I need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.

View 7 Replies View Related

Nested If Statement That Doesn't Give Blank Cell When It Is Evaluated?

Aug 29, 2012

I have a nested if statement that doesn't give a blank cell when it is evaluated. The cell is general formatted but results in a '0' in the cell instead of a completely blank cell.

If statement is:
=IF('Sales info'!B16="",'Sales info'!B6,IF('Sales info'!B6="","",'Sales info'!B16))

Why is this giving me a 0?

View 9 Replies View Related

Making #N/A Dissapear (or Appear Blank)

Apr 15, 2009

How do you make a cell appear blank when it has a formula in it refering to a cell that hasnt been used yet?

I would like some cells to be blank instead of having #N/A in them.

View 7 Replies View Related

Making The #DIV/0! Error Blank

Jun 19, 2006

Im using the below calculation that keeps returning a # DIV/0! error if no data is present, so I have tried to use the ISERROR to make it blank, excel takes the below calculation but still shows the #DIV/0!

=IF(ISERROR( SUMIF('Data Input'!$I$2:$J$1000,AA5,'Data Input'!$J$2:$J$1000)+SUMIF('Data Input'!$I$2:$J$1000,Z5,'Data Input'!$J$2:$J$1000))/(COUNTIF('Data Input'!$D$2:$D$1000,B5)),"",(SUMIF('Data Input'!$I$2:$J$1000,AA5,'Data Input'!$J$2:$J$1000)+SUMIF('Data Input'!$I$2:$J$1000,Z5,'Data Input'!$J$2:$J$1000))/(COUNTIF('Data Input'!$D$2:$D$1000,B5)))

View 9 Replies View Related

If/Then Statement: "If Cell A1 Of Sheet 1 = "a", Then I Want To Put The Contents Of Cell B2

Oct 27, 2009

See the attached example spreadsheet which will make this much easier to understand. I want to create a formula in the second worksheet that says "If cell A1 of sheet 1 = "a", Then i want to put the contents of cell B2 ("flour") into cell A1 of sheet 2, I want to put the contents of cell C2 ("egg") into cell B1 of sheet 2 and I want to put the contents of cell D2 ("sugar") into cell C1 of sheet 2. If not put 0.

View 3 Replies View Related


Jul 29, 2008

I am trying to write a statement as follows. IF CELLK11 IS NOT VALUE BLANK AND F11=BLANK THEN TRUE = 1 FALSE = 0.

View 3 Replies View Related

Compare Cell Contents With Given Range Of Contents

Dec 30, 2013

I have attached a workbook stating my problem.


View 10 Replies View Related

Copyrights 2005-15, All rights reserved