# Make A Formula Output A Blank Cell?

Mar 20, 2009I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:

=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

A B C

10.1810.82

In column A i am using the following formula to output "Our Apple" when "Apple" is in column D and "Our Banana" when "Banana" is in column E:

[Code] .....

I would like the formula to also output "Big Apple" immediately underneath "Our Apple" when its present, and output "Big Banana" immediately below "Our Banana" when it is present.

The yellow column is how column A should look after the formula edit.

Attached File : Book2.xlsxâ€Ž

So I know how to make the textbox output whatever a specific cell says, but is there a way to do the opposite? make the textbox the input and have the cell changed based on the input of the textbox?

View 3 Replies View RelatedI need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:

=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")

I am using a “borrowed” formula from a coworker that I do not really understand however it is working for me, sort of.

=+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V17,$W17,$X17,$Y17,$Z17,$AB17,$AC17,$AD17,$AO17,$AZ17)))),I F(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="FFFFFFFFFFFFF FFFFF","FAIL","PASS"),"FAIL *")

What this does is read the range of cells and immediately fails a quality check if it is in a critical cell, or fails if multiple non critical items fail.

I want to use an if/then to make cell blank if there is no data in one particular cell (which means no quality inspection performed). Here is what I tried:

IF(V18="","",+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V18,$W18,$X18,$Y18,$Z18,$AB18,$AC18,$AD18,$AO18 ,$AZ18)))),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="F FFFFFFFFFFFFFFFFF","FAIL","PASS"),"FAIL *"))

The leaving the cell blank part of the formula works however it allows the remainder of the formula to only work if “fail” appears in the first cell it reads (in this case V18). If the “fail” appears in any of the other critical cells, it does not work at all.

Also as a side project not sure what the substituting to $R$2 does so if anyone knows how to clean this up, let me know.

I have 2 worksheets, i have one sheet that has a form with lists, when nothing is selected the cells are blank. On the other sheet I have a few cells which = some of the sheets on the other worksheet. when nothing is selected, the cell displays 0 even though the cell its supposed to be displaying is blank.

View 4 Replies View RelatedHow do I put in iserror in to this to make the cell go blank with N/A is returned. Only ever done this on vlookups. =IF(I15<E8,"TOOLING REQUIRES CALIBRATION","")

View 2 Replies View RelatedHow can I make a cell display 0 or remain blank instead of #N/A?

I have several cells that are either VLOOKUP or just normal sum functions but they all display #N/A.

I have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?

View 9 Replies View RelatedAny way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.

[Code] ......

First, I can get the balance formula, and when I drag the square at the bottom right, I am able to drag the entire formula down...I want to do that. However, when I drag it down, the number keeps popping up. I was wondering if there is a way to be able to drag the entire thing down and be able to make it so that it is hidden (the cell is blank) unless the debit or credit cell in the corresponding row is filled. Also, if possible, I want to be able to drag it down to infinity, so it can go on forever, so that there is no need to always drag it down when I add more things in it. I am attaching what I have till now.

View 5 Replies View Relatedsection 100

section 200

100-1

200-1

I have a large spreadsheet with text, numbers and blank cells. Is there a code a can use to make all blank cell have a dash (-) without affecting the other cell with data.

I am calculating blood sugars for my daughter and putting them in a spreadsheet. The formula is simple:

Blood sugar - target blood sugar / 20 which yields the amount of insulin that she needs to take. I want this cell to be blank unless I enter a value in the cell above it.

Next, I take the value from that formula and add it to the amount of insulin that she takes for eating.

The value in the top formula can be 0 or less, but, if the value in the bottom one is less than 0, I want the cell to be blank.

is there a formula that would have as it's output the cell address of the

same cell where the formula has been entered.

e.g. In cell A10 I input a formula whose output is the cell reference A10.

Does anyone know how to use a macro to make a cell background blank if it is not orange or dark blue?

View 12 Replies View RelatedI want to make a cell blank if nothing is written in another cell. I have used this formula in C4: =IF(ISNUMBER(C3),IF(C3<180,C3+180,C3-180),"")

View 1 Replies View RelatedMake a cell blank if another cell is blank? This is my formula, it checks two dates. It is in cell M5.

View 4 Replies View RelatedI've got the following sumproduct formula (which seems to work):

=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))

B17 is a keyword to be found in the Product_Keywords range

I'd like to modify it so that:

ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1

Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,

but how would I keep track of the number of commas in the cell?

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

View 8 Replies View RelatedI have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).

What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)

I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...

View 2 Replies View RelatedMy workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).

The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:

Code:

=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),

YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))

The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".

[URL] ...........

I have a large spreadsheet with many pieces of equipment in it. There are a few piece that I would like to highlight should thier computer license go out of date, or even better 30 days prior. I have a Feild with the Expire dates but it is at the end of the spreadsheet not in plain sight. Is there a way to make my Equipment description column highlight when the date in the other column reaches the current date (or 30 Days before). I have cut and pasted the part of the spreadsheet into a new file to make it easier to read and hid all the none important feilds.

View 5 Replies View RelatedHeres my current formula but its justreturning text i would like it to return the cells value

=IF((J2>K2);"value=J2";"(value=k2)")

I have on my Sheet named "Data" in Cell K4

=CONCATENATE(L16,O25,L18,O25,K20,S25,N22)

The values of that cell become a formula.

I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.

The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.

Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?

I have a formula that combines the the text of various cells.

Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.

See formula below:

I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32

There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.

How would you write a formula where the sum depends on what dates you enter in A1 and B1?

Date cells C1 to C365

A1 start date

B1 end date

Example:

A1 = 1/1/14

B1 = 1/31/14

So cells C1 to C31 will be added.

"=sum(c1:offset(c1,(b1-a1),0))

This formula works, except if you enter a different starting dates, it just counts from c1.

I'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?

View 8 Replies View Related#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A

=RtHistory("IDN",B5,B7:D7,B6,"LAY:HOR NULL:SKIP ZERO:SKIP SORT:ASC")

the #N/A errors are based on the line above. Is there a way to remove the #N/A? I want to make Row B/C/D above blank

I have column g as a dollar amount, and a running total in column h, where g4+h3=h4. I want the cells in h to appear blank until there is a dollar amount entered into the corresponding g cell.

View 10 Replies View RelatedI get reports weekly that contain too much information. Using "IF - LIKE - THEN" in my macro I copy and paste only the relevent data from the new work sheet to a separate worksheet.

It works fine except, it pastes over the previous data instead of below it overwriting the old info. Please have a look at my code, How do I instruct it to paste in the first empty row?

I want to make all the cells that are outputted as blank by my formula a certain color to distinguish them from non blank cells. my current formula: [B1=IF(A1=0,"",A1)]. my guess would be to use an [AND] function but I do not know how to command a cell to change color without using the toolbar.

View 4 Replies View RelatedOk, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.

I tried this but it's not working:

=IF(C3="0","",C3-C2)

I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:

Dim rng As Range, aCell As Range

Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")

For Each aCell In rng

Selection.End(xlDown).Select

Application.CutCopyMode = False

[Code] .......

It does not go to the next column, instead it stays in the same column and repeats the process.

I have a spreadsheet which projects the amount of work on hand each week. It runs up to the end of the year and shows the estimated figures and then each week I input the actual figures. The way it works at the moment is that it looks at the estimated work on hand, adds the work on which has been received and then takes off the work that has been cleared...this then gives an estimated level of on hand work.

What I want it to do is to look at the cell which would show the actual work on hand and if thats empty then go on to look at the estimated work on hand. In other words - I want a formula that looks at a cell to see if there's a figure in it and if not uses another cell instead. I have tried: =If(u5=0,r5+y5-z5)

but when I input a figure into U5 it inputs FALSE into the cell. I don't want it to do that, I want it to look in cell U5 and if there's something there then do U5+Y5-Z5.....and if U5 is empty then do R5+Y5-Z5

How do you write a code that will calculate a formula and output the result in a cell?

For example:

A1 = 1 (Named "Cell1")

A2 = 2 (Named "Cell2")

A3 = Results of macro for Cell1+Cell2 = 3

Is there a way to write it if I also want B3 = B1 + B2 and C3 = C1 + C2, etc.

I am trying to get a list of states, based on their expiration date.

I have all 51 states listed, some states have 2, 3 or more licenses, so I created a column that I hid of the state abbreviation, for the use of formulas.

If the license expires in 30 days or less I want to display the state abbreviation. I couldnt figure out how to do an array output, so this is what I am trying to come up with.

=IF(COUNTIF('Kevin Doyle'!F5:F250, "

I am using a IF(ISNA(Vlookup##,##,##)),"",(Vlookup##,##,##)) function, in order to remove N/A errors.

Is there anyway to convert the "" values to back to an empty cell without anything in it (i.e not text "", but empty as it was originally). I find that the file sizes are very large, when using this function, as data (although blank) is stored in each cell (i know this, because if I use cntr+arrow, it sees it as data, and not an empty area). I don't want to manually go through each cell and delete them as there is quite a bit of data (10000 rows , 2 columns, 30 tabs)

I have a formula in cel B1 : =SUM(A2:A100) / A1

I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.

So I would need something like (literally):

=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)

ADVERTISEMENT