Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










If No Data Leave It Blank (cell Contains Formula)


A1 has a date of 03/10/07
B2 has formula =A1 + 7
I want to copy that formula down the column B2 but if
B3 is empty I get 01/07/1900
How Do I make so that if there's no date entered in
A column I get blank cells but still retain formulas,
I've been running into this issue with other formulas but I couldn't find any solution searching this forum,


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Leave Cell Blank From Formula Until Data Is Posted
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?

View Replies!   View Related
Formula To Leave A Cell Blank
Probably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.

View Replies!   View Related
Leave Other Cells Blank If No Data In Cell
I have 2 similar question.

I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)

The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.

If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.

I think it has to do with using an IF statement followed with ""? Am I on the right track?

Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?

I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.

View Replies!   View Related
Conditional, Variable Formula: Leave Blank Or Put A 1
I could really do with is this: =IF(F4>0,F4*3.4%+0.2,"") but I need to be able to make it either 3.4% OR 3.9%. What I thought was if I use another cell, say F3 which I can leave blank or put a 1 in, and tell it if it has a 1 it is 3.9% + 0.20 or if F3 is blank then it is 3.4% + 0.20.

View Replies!   View Related
Formula To Sort And Leave Spaces Blank Where One Column Doenst Have The Same Value
Every AM I run a report that has ALL of our company order numbers from the 2 systems we use. I get those reports and put them into 2 columns. instead of manually inserting so they all match up, is there a way to do this automatically? ....

View Replies!   View Related
Leave The Cell If Its Blank
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.

************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View Replies!   View Related
Leave Target Cell Blank
Every time I think I've got this thing beat, they throw another curve at me!

If cell Q19 is blank, leave target cell blank
If cell Q19 is not blank, return the value of cell E$4

View Replies!   View Related
If A1,A2orA3 Is Greater Than 0 Then Multiply By 2 Or Leave The Cell Blank
=IF(OR(A1>0,A2>0,A3>0),*2,"")

if A1,A2orA3 is greater than 0 then multpy by 2 or leave the cell blank

View Replies!   View Related
If Tab Exists Put Cell, If Not, Leave Blank
Excel Version: 2000

Workbook has the possiblities of 366 tabs at the bottom, but for a normal calendar year will only have 26.

The tab name is the ending date of the pay period without the year. So for Feb 25, 2007 the tab would be named 0225

On a yearly calculations page I am simply trying to say IF TAB 0225 exists then cell equals '0225'!A2 otherwise the cell should be blank.

I tried this, but gets a reference failure instead of putting nothing...

=IF('0102'!A2=0,"",'0102'!A2)

In this example the possible tab name would be 0102. If the tab exists, everything works fine, but since in this case it doesn't, I get a reference failure. #REF!

I NEED the result to be empty if the tab doesn't exist.

Any idea would be helpful. I'd rather not have 366 tabs when only 26 are needed for any calendar year.

View Replies!   View Related
Combining Names: If The Cell Is Blank The Result Should Not Leave A Space
I have 6 columns and would like to combine them. If the cell is blank the result should not leave a space.

- Prefix
- First Name
- Last Name
- Middle Initial
- Last Name
- Suffix

Example: Mr. Henry J. Weeks, III
Example: Henry Weeks
Example: Mr. Weeks
Example: Henry J. Weeks, III

View Replies!   View Related
Leave Cell Empty Even When There Is A Formula In It
Its probably very simple question, but i can not work it out.

I am trying to design a simple worksheet so I can keep track of how much money my room mate has paid me and what she owes. She pays $142 a week rent plus $23 a month for internet.

Please open the attached workbook. You can see in E7 and 8 there is a red negative number. This is because my tenant hasn't paid me yet for those weeks (D7 & 8).

How can i get these cells to read $0 or leave the cell empty?

The reason why I need this to read $0 or nothing is because it is messing up my next calculation in D3 where it works out how much she has paid for our internet bill.

Thanking you in advance. And sorry if this is a really daft question. Its probably something really simple i have missed.

View Replies!   View Related
Clear Value Cell & Leave Formula Cells
The worksheet (Payments) contains a list that allows the user to input accounting transactions. Almost every input cell contains a formula or data validation enabling selection from a dropdown.

The worksheet feeds the data to 3 separate spreadsheets (Debits / Credits / VAT) and the formulas for that are contained within dynamic ranges in the other 3 sheets. A command button macro then feeds that data from the 3 sheets to the Master sheet. This all works perfectly.

After the macro is run and the data is copied to the Master sheet, I would like all of the data in the 4 other sheets to be cleared without losing the formulas and data validation, ie so that the sheets are empty and clear for the next batch of inputs.

View Replies!   View Related
Macro To Clear Some Data Within A Cell But Leave Other Data
What i want to do is clear some data within a merged cell but leave the rest.

I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......

View Replies!   View Related
Leave Cells Blank Without Zeros
i have a mock up football issue here i want to enter scores in sheet 1 and they automatically fill sheet 2 etc i know i can just =and copy sheet 1 A5 and so so but that leaves zeros which will start allmy formulas with 1 point i need a formula that will leave sheet 2 3 etc blank untill any score is entered

View Replies!   View Related
Sum If Values Entered If Not Leave Blank
I need a formula that will do the following:

Sum K4 (unit price) and M4 (shipping) and return the answer in N4 (total), if K4 and M4 are empty then leave cell N4 blank

All responses will be welcome as this has been driving me mad for over an hour, and the answer is probably so simple!

View Replies!   View Related
Conditional Format; Put 1, 0, Or Leave Blank
I have cells B5:AL22 I want to put 1, 0, or leave blank. I need the zero (0) to turn red with white letter, the 1 and blank cells to remain in the present format. I can get the 1 and zero (0), but I don't know how to get the blank cells to stay in regular format.

View Replies!   View Related
Employee Leave Tracker & Check Leave Time Is Valid
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things

1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.

2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.

View Replies!   View Related
Formula - To Calculate Occurences Of Sick Leave
I have been trying to come up with a formula that helps calculate the number of times one of my employees has taken sick leave. The instances do not mean number of times they have taken a medical leave but the number of instances. For ex: In the month of Sept, my employee takes a medical leave on 1st Sept, then another on 7th Sept and then 3 days of medical leave on 12,13, and 14th Sept. So the total medical leaves is equal to 5, but the number of instances is 3.

The formula should be able to provide a result of 3.

View Replies!   View Related
How To Leave Certain Rows With Certain Data
I have an Excel file with over 5000 rows all containing URLs

I only need the URLs containing a particular word in ther such as
if the URL contains ".edu" leave it, and if not, delete the data.

Is there a way to do this by VBA?

View Replies!   View Related
Compare And Leave Only Unique Data In Sheet
what i want to do is use 2 sets of data ss is attached to help understand

i would like to compare hometeam v player

therefore any games that steven gerrard didnt play in ( in the example )
it would leave all the games on sheet 3 where he wasnt involved.

hometeam has all the games liverpool involved in
player has all the games steven gerrard involved in

cross reference the 2 to leave all the games he wasnt involved in on a new tab

must be possible just not sure if i need look up or a macro

View Replies!   View Related
Delete Filtered Data, Leave Headers
I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.

How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)

View Replies!   View Related
Leave Only The Number Inside All Cell
Each cell below contain a number and a time + ET. if like say i want to delete the time + ET and leave only the number inside all cell.


1.1425 8:20pm ET ON A1
3.0805 6:10pm ET ON A2
814.30 6:42am ET ON A3
798.70 11:27am ET ON A4
380.00 9:10pm ET ON A5

View Replies!   View Related
Leave Empty Cell In A Color.
How is it possible to leave a cell in a set color, if nothing is entered in the cell? And is it possible to merge it with it with existing formulas allready in the cell?

View Replies!   View Related
Fields That Containa Formula Need To Appear Blank Until Data Entered
i have two date fields....one date requested eg 02/06/09 (cell C10)...another date completed eg 03/06/09 (cell R10)

a third field (cell S10) contains the formula: =IF(R10-C10=0,"less than a day",R10-C10)

so if a request was actioned on the day then it shows as less than a day, otherwise will show how many days it took

but when this formula is draggeddown all other cells show - less than a day

how can i make these cells blank whilst still holding the formula?

also - is there a quick formula to add to show only the amount of wrking days a request took to complete?

View Replies!   View Related
Formula Which Changes If Cell Is Blank
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

View Replies!   View Related
Array Formula: How To Aggegrate Data, Removing Blank Cells
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.

ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...

I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.

so the new column would be:

--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007

I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.

=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))

View Replies!   View Related
Convert Blank Formula Cell To Empty Cell
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)

View Replies!   View Related
Enter Date/time Cell Was Filled, And Then Leave It ()
I have a spreadsheet in which I want to track the date and time someone made an entry in an adjacent cell (so if someone enters something in B2, I want the date/time of that cell edit to appear in C2).

When the date/time has appeared, I want it to remain the same and not update when the worksheet is opened the next time. So I can't use NOW to pull it in, because that's going to change every time the file is opened.

View Replies!   View Related
Formula To Make Cell Blank Instead Of Zero
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

View Replies!   View Related
Blank Cell Shows 0 Or 12:00 AM If There Is Formula
I am getting 0 or 12:00 AM when I format cell as time and put formula.

I working on timesheet using this formula (=SUMIF('2'!G4,"="&TODAY(),'2'!E17)). If date in G4 on sheet2 match with todays/current date then copy data in cell E17 to sheet3(b11).

G4 = todays date
E17 = time eg. 2:25 AM ( I have to format destination cell as time because E17 has time value

Formula works fine but when there is no data in E17 or E17 is blank then my destination cell shows 12:00 AM.

View Replies!   View Related
Formula In Cell Show Blank If Zero
I have a spreadsheet which imports data from worksheets week 1, 2 etc to a monthly summary.
Is there any way I can show a cell as blank if the return equals 0?

I'm sure the resolution is probably simple but then so am I.

View Replies!   View Related
To Return A Blank Cell In A Formula
i have IF sentence that returns either a number or a blank cell (tried using "") in a colum. This data is entered in a chart.

My problem is that that excel dont recognise the "" as a blank cell in my chart.

Is there a way to do this? i could maybe use VBA to make my own function that clear.contents if the cell value is smaler than say 1?

View Replies!   View Related
Subtracting Column B From A, And Leave Third Column Blank If No Value In Column B?
I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.

If there is no value in column B, then I want that row in Column C to just stay blank.

View Replies!   View Related
Fill Formula To Last Blank Cell In Column
I have a formula

=LEFT(L2,FIND(".",L2,1)-1)

I need a macro to copy this to the last blank cell in the column, the last blank cell will be different each month otherwaise i would have just been able to enter a range L2:L6000.


View Replies!   View Related
Formula Cell To Be Blank (Vlookup Returning #N/A)
If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?

This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)

I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))

View Replies!   View Related
Formula To Show A Blank Cell When A SUM = Zero
On my spreadsheet i have 4 columns which are in currency format. The 5th column (total) adds the 4 up which currently looks like this - =SUM(BG44:BG45) this shows £0.00 in the 5th column.

What i need is a formula in the 5th column (total) so that when it adds up the 4 previous columns if the sum = £0.00 then the total column should show a blank cell.

View Replies!   View Related
Handling Error (#VALUE) In Formula With Blank Cell?
When I enter this formula I get a #VALUE! error. I think it's because cell AG46 has an IF formula that results in "blank" if 0. How do I fix this error so that it includes cell AG46 and if cell AG46 is blank it counts it as a zero?

=SUM(A12)-(AG46*8)(=40 is the answer I'm looking for)
=SUM(40)-(0*8)

View Replies!   View Related
Make A Formula Output A Blank Cell?
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.

View Replies!   View Related
Using IF(OR And IF(ISERROR In Same Formula: Return A Blank Cell
I am trying to fix the below formula =IF(OR(ISERROR((F26-E26)/F26),((F26-E26)/F26)=1),"",(F26-E26)/F26). If I get an error from the formula I want it to return a blank cell. If I get 1 as the answer to the formula I'd like it to return a blank cell. This formula only works if the result is 1 but won't return a blank cell if the answer is an error.

View Replies!   View Related
Average Formula Dropping Lowest Value Or Blank Cell
I need to get a formula to calculate the average of the best 3 scores out of 4, but there is some that do not have a value in a cell (so some are only out of 3 scores not 4) and if i simply drop the lowest value and sum the rest, it will incorrectly calculate the average.

View Replies!   View Related
Display/Show Blank Cell Via IF Formula/Function
The following are on a sheet:

A1 = 5700
B1 = 235
C1 = 17:14
D1 = 5922
$E$1 = 09:01
$F$1= 3

$E$1 and $F$1 are (the only) absolutes/constants.

A2 contains the following:

=IF(A1>D1,B1,IF(A1+B1<D1,"d",IF(AND(C1<$E$1,B1<$F$1),"",B1))))

This translates as:

=IF(5700>5922,235,IF(5700+235<5922,"d",IF(AND(7:14<09:01,235<3),"",235))))

The result is a variable/number (235), "d" or a blank cell ("").

This formula is in a column and works fine where there are numbers in corresponding cells. The problem arises with corresponding cells which appear blank (show no values) but contain references to other cells: they result in a "d" when nothing should be displayed.

So while a corresponding blank cell is correct if it shows no values, it gives me this problem - I don't want "d" or anything.

View Replies!   View Related
Correction Of Formula: When The Result Is 0 It Just Leaves A Blank Cell Without A 0
I have this Formula in a excel sheet =IF(AND(COUNT(F12;G12)=2;G12<F12);G12-F12;IF(AND(COUNT(F12;G12)=2;G12>F12);ABS(F12-G12);""))

My problem is that when the result is 0 it just leaves a blank cell without a 0.
Can i correct this so that when the result is 0 it will actually show a 0 in the cell? I can´t put a 0 in the end of the formula because then it shows zeroes in all the cells without information aswell.

View Replies!   View Related
Next Available Data Or Blank Cell
For cell K12, check for data in cells A4:A6 and F4:F6. If data is found, put it in K12, but if no data is found, cell is to remain blank.

Attached is the Excel file in question with comments in the appropriate spots.

The equation currently being used:
{=INDEX(A4:A6,MATCH(TRUE,A4:A6<>"",0))}

View Replies!   View Related
Conditional Format Formula: Allow The User To Blank Out The Cell Within The Year
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck

View Replies!   View Related
IF A Cell Is Not Having Data Show It As Blank
there is data going to excel from database. The data is something like jan to dec sales and in a arbitrary fashion. now if there wont be data availble for say month of july then nothing will be there.

Now i need to nicely formulate data from jan feb ..Dec and in same order in another cells. Now for empty cells data after formualting it is coming as #N/A. and by this i am getting a same thing in the application where this excel sheet is being used. So for eliminating it i need to use 'if' such that if it is undefined or NULL then blank should be there in the formulated cell.


View Replies!   View Related
Copy Cell Data If Cell Blank
I have another query that I believe can only be solved using VBA.

In a row of input data, some cells are duplicated from previous cells in the same row but in some instances, the user needs to be able to input different information.

e.g. M4=F4 if left blank and the same would apply to columns N=G, O=H, P=I & Q=J.

I have done some searching but nothing quite matches my requirements and recording a macro only produces the following result.

View Replies!   View Related
How To Automatically Copy Data From Any Blank Cell Above?
I receive a huge spreadsheet with account numbers in col A and names in col b with dates in col g and amounts in h. However if there is more than one date and amount for the same account number and name, the people/program who originated the data left those fields blank.

In order to sort the list by name by date, I have been manually copying the data from the row immediately above to the blank row(s). (L O N G process) So if I have ...

View Replies!   View Related
Moving Data From One Column To Another But Only If Cell Is Blank.
I need to move data from columns B & C into A without losing current data in column A (see attachment). I'm sure I know how to do this but cannot for the life of me remember how.

View Replies!   View Related
Getting A Calculated Cell To Be Blank If There Is Not Data To Be Computed
I am having problem with getting a calculated cell to be blank if there is not data to be computed.

View Replies!   View Related
Auto Sum Data In First Blank Cell
I have this code here that is supposed to Auto-sum 3 columns for me...

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved