Keep Cell Blank If Referencing Cell Within IF Formula Is Blank

Jul 28, 2014

My 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] ...........

View 2 Replies


ADVERTISEMENT

Blank Cell Referencing

Mar 2, 2009

On the spreadsheet Im creating I am using cell referencing. The user will input data into the blank cells, and cell referencing will put them into another table. Then I add these using an IF(OR(ISBLANK formula.

However, the empty cells come through as 0's rather than a blank cell. As they're coming through as 0's, they're being calculated into the formula, and this is causing problems in my main table.
Is there a formula I can use to ensure that the empty cells being copied over do not give off any other value?

I tried to use a few different IF formulas to only let specific data go through, but it didn't work. I tried to have the blank cells produce a letter so that they wouldn't be calculted in the formula, but letters are also counted as measured values.

View 14 Replies View Related

Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

Aug 2, 2014

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 Related

IF Blank Or If Not Blank Function That Works With Adjacent Cell With Formula In It?

Aug 12, 2014

I 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?)

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

How To Populate Blank Cells With Sequence Until Non Blank Cell Is Encountered

Mar 22, 2014

I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.

What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).

This all needs to be done in Arial, 10pt, white.

View 10 Replies View Related

List - Filling In Each Blank Cells With Value Contained In First Non-blank Cell Above It

Feb 27, 2013

I have a list that looks something like this:

Column B

Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5

The range of cells in column B containing the items has a name "ColStreams"

I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.

View 2 Replies View Related

Replace #VALUE! Error For Blank Cells With Blank Cell

Nov 17, 2008

The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.

=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))

View 9 Replies View Related

Formula To Create Blank Cell Based On Dollar Amount In Another Cell?

Jan 7, 2014

Ok, 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)

View 2 Replies View Related

Find Last Cell In Column And Paste Formula In Next Blank Cell Then Repeat On Remaining Columns

Jan 14, 2013

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.

View 8 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

Convert Blank Formula Cell To Empty Cell?

Feb 26, 2008

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 5 Replies View Related

Formula With Offset From Active Cell And Sum Until Blank Cell

Dec 24, 2013

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)

View 3 Replies View Related

Convert Blank Formula Cell To Empty Cell

Feb 26, 2008

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 2 Replies View Related

Insert Blank Row If Adjacent Cell Value Is Blank

Jan 7, 2013

If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.

View 9 Replies View Related

Return Blank If Concatenating With Blank Cell?

Apr 3, 2014

in one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.

View 8 Replies View Related

Copy Or Create 'blank Cell' As 'blank'

Feb 4, 2009

using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..

View 9 Replies View Related

First Blank Cell In Column & Offset To 1st Blank In Row

Jul 11, 2007

I have checked archives, some similiar but not quite what I want.

In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.

View 9 Replies View Related

Formula Which Changes If Cell Is Blank

Jul 19, 2006

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 4 Replies View Related

Formula In Cell Show Blank If Zero

Jun 4, 2009

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 6 Replies View Related

Formula To Leave A Cell Blank

Aug 25, 2009

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 4 Replies View Related

Formula To Show A Blank Cell When A SUM = Zero

Oct 29, 2009

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 3 Replies View Related

Formula To Make Cell Blank Instead Of Zero

Oct 29, 2009

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 14 Replies View Related

Blank Cell Shows 0 Or 12:00 AM If There Is Formula

May 25, 2009

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 7 Replies View Related

If Formula - Returns Zero For Blank Cell?

Jan 12, 2012

I'm using the following formula, and keep getting a zero when the cells in D4 are blank when I simply want exactly whatever is in D4 to be shown...

=IF($B$2="Alness",Alness!D4,IF($B$2="Amsterdam",Amsterdam!D4,IF($B$2
="Atlanta",Atlanta!D4,IF($B$2="Budapest",Budapest!D4,IF($B$2
="Dalian",Dalian!D4,IF($B$2="Debrecen",Debrecen!D4,IF($B$2
="Durban",Durban!D4,IF($B$2="Edinburgh",Edinburgh!D4,IF($B$2

[Code] ..........

View 9 Replies View Related

Formula To Return Zero If Cell Is Blank?

May 8, 2012

I have a column of imported data like this, with scattered blank cells:

Rd Frnt200634350464300399636350

I am doing a multiple regression where the above are independent variables. I usually have hundreds of variables in a column like this. Because each cell must have a value in it (the regression won't accept blanks), rather than manually fill the blank cells with a 0, I set up an adjacent column to convert the blanks to a 0 and regress off that column. But every formula I try still produces a blank in that adjacent column. I have tried things like: =IF(A1="",0,A1) but they do not work. I always get a blank in the adjacent column when the original cell is blank. Can't get a 0 in there.

View 7 Replies View Related

Formula Which Can Calculate Row 60 For Blank Cell?

Jul 23, 2012

I need a formula which can calculate row 60 for blank cell. If true then apply another condition which checks whether or not the cell above the row 60 up-til row 50 has any bold fonts or not. If true then show True and if false, show False in cell J1.

View 1 Replies View Related

Drop Out Of Formula If Cell Is Blank

Jun 11, 2014

How can I add:

IF statement within this formula =IF(ISERROR(LEFT(E1,FIND("x",E1)-1)="0"),"Special",(LEFT(E1,FIND("x",E1)-1)))

To drop out if E1 is blank

Say the formula is in F1 cell and it is checking E1 and E1 is blank, F1 would be blank.

View 6 Replies View Related

To Return A Blank Cell In A Formula

Mar 4, 2010

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 9 Replies View Related

Handling Error (#VALUE) In Formula With Blank Cell?

Oct 9, 2008

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 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved