Getting Zero Formula Field When Data Field Blank / Empty

Feb 13, 2013

I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.

View 5 Replies


Counting Per Date Field And Data Field

Apr 29, 2006

I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.


See Attached

View 4 Replies View Related

Pivot Table Is Displaying 1/29/14 For Every Cell In Particular Data Field That Is Blank

Jan 23, 2014

On my data tab I've got a column for "review date." Some of those cells are blank. When you go to the pivot table, the respective cell for that date cell displays the date 1/29/14. There is no data in the cell on the data tab, so why would it be displaying 1/29/14? I want it to either say "blank" or just be blank. It does this for every review date cell that is blank.

View 3 Replies View Related

Auto Populate Field With Date When Other Field Is Selected From Validation List

Jul 4, 2013

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.

The information is currently part of a Table in Excel 2007 that has about 500 records.



Month Completed

In Progress




Data Validation in Spreadsheet

Input 1
Input 2



View 2 Replies View Related

Macro To Preset Field With Text If Condition In Another Field Is True

Jun 9, 2006

I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.

View 5 Replies View Related

Change/Move Pivot Table Row Field To Column Field

Apr 23, 2008

In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?

View 3 Replies View Related

IF Field Is Full Use Or If Empty I Need To Use Price

Sep 29, 2008

Price - Bulk Price - Total price

And i need to calculate the total price (totalprice = (PRICE OR bulkPRICE * 2.5)

If Bulkprice contains a number i need to use that field but if it's empty i need to use Price.

View 14 Replies View Related

Auto Populate A Field In One Tab By Comparing Another Field In A Different Tab?

Jan 25, 2013

I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.

I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.

View 2 Replies View Related

Changing A Numerical Field To A Date Field

Dec 28, 2006

in coverting these fields into a date field.

Example 91306 to 060913

I have encloed the file.

View 14 Replies View Related

Inserting A Total Amount Field In Each Empty Row

Apr 21, 2009

to run a macro that enters a new row after each set of currencies (which have been sorted). I now need to enter a total balance cell within this empty row which calculates the total amount for each currency.

I need to do this using the SUMIF function below:

the above basically lets me calculate the total but as no - and + signs were input it does this by recognising the 'dr' and 'cr' in the column next to the amount.

View 9 Replies View Related

If A Result Is 0 Can A Field Be Blank?

Mar 23, 2009

H2 formula is =OFFSET(A4,MATCH(MAX(D$4:D$1000),D$4:D$1000,0)-1,0)
I2 formula is =MAX(D4:D26)

If either of these result in a ZERO is it possible to leave the field blank?

View 6 Replies View Related

#div/o Error In A Blank Fiield Or Field With A 0..

Jan 15, 2009

I tried to read the post and figure it for myself, however close I have not been successful at eliminating this error(#div/0)in a blank fiield or field with a 0.
Below is the formula can someone please help with the IF portion that I can use to correct this statement. =SUM(B12:E12)/F12

View 3 Replies View Related

Get =IF(LEN(I3),I3,J3) To Show As Blank Field When No Result

Feb 5, 2009


at the moment if there is no result it equals 0

I thought this might work..


View 9 Replies View Related

DSUM When Criteria Field Is Blank

Jun 1, 2007

I have a DSUM function with multiple criteria that are linked to user selections on a different sheet. If the user doesn't set a value for a particular field, the cell is blank and the DSUM should sum records with both blanks and non-blanks in that field.

Unfortunately, simply setting my criteria formula equal to that cell does not accomplish this. I've implemented an if statement to help but am having trouble defining a single criteria for returning everything.

Simply pointing to named range doesn't work for blanks and nonblanks when the cell is empty because it returns a zero: =SelectedValue

Criteria formula #1 I'm using: =if(SelectedValue="",="=*",SelectedValue)
Criteria formula #2 I'm using: =if(SelectedValue="",="<>*",SelectedValue)

When the SelectedValue cell is blank, these two formulas ensure that all records are summed. I would like a criteria that sums both blanks (=*) and non-blanks (<>*) without having to use two separate cells for the criteria. This gets unwieldy when trying to combine it with criteria for other filter fields.

View 9 Replies View Related

Enter Blank Row Every Time 2ND Character Of Field Changes

Oct 31, 2011

I am trying to enter a blank row everytime the 2ND character of a field changes...

sample cells (ALWAYS COLUMN B)(last row needs to be calculated as it changes daily):


when done:


was trying this:

Dim chkConfirmRw, LastNameRow As Integer
For chkConfirmRw = LastNameRow To 1 Step -1
'Compare the current cell to the one below it
'If they don't match, insert a row row below the current Row
If Range("B" & chkConfirmRw) Range("B" & chkConfirmRw + 1) Then
Range("B" & chkConfirmRw + 1).EntireRow.Insert Shift:=xlDown
End If
'Decrement the counter and do it again

but it does not work.

View 3 Replies View Related

How To Show All Results If Parameter Field Left Blank

Nov 3, 2013

I have three search boxes in XL , two are search from and to dates and the other is search be ref.

I can currently search by dates and ref but would like to show all records in the specified dates if the ref field is left blank.

I am using the query writer and referencing back to cells in XL. I don't use the wizard or SQL. I have tried many options and think I should be using a OR statement with a wildcard but can't seem to get the result, does the wildcard just show the blank cells in the actual data?

View 1 Replies View Related

Leave Fields Blank Until Number Is Entered In Other Field?

Jan 15, 2014

I created this formula =G2*2.9%+.3

what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88

the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30

How do I prevent the formula from calculating if the G column is blank?

View 3 Replies View Related

Concatenate Two Text Fields BUT Left Adjust First Field And Right Adjust Second Field

Jun 22, 2012

I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.

A1 = "John Williams", A2= "Single"

A3 = "John Williams Single"

View 1 Replies View Related

AutoFilter Multiple Field Automatically (and Show All If Criteria Is Blank)

Jun 11, 2014

I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text
End If
End Sub

View 3 Replies View Related

Subtracting Dates Results In A Big Number When A Date Field Is Blank

Jul 11, 2009

How can I change the ....

View 14 Replies View Related

(multiple) VLOOKUP Function To Return Blank Field On False

Mar 10, 2009

Where in this function would I put " " to return a blank field if false is returned?


View 4 Replies View Related

Extract Zip Code From Address Field - Return Blank If Not Found

Jul 17, 2013

I'm very new to excel. I need a formula to put in a column (I) that returns ONLY a five-digit zip code from the adjacent cell in column J, which is a full address. Nearly every entry is written differently, and many do not contain a zip code. I would like the cell to be blank if the cell in J contains no zip code .

I'm using the formula:

This formula sometimes returns -**** format numbers, considering them to be negative and still five-digit. It also returns #N/A if no zip code is present, and I would like this to be blank. I'm sure I can nest the formula within an IFF, but have not been able to make it work myself.

View 4 Replies View Related

Copy Correct Multiple Choice Answer Into Blank Field Based On Corresponding Number Identifier

Mar 23, 2014

Here's an example of what i want to do

A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3

I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.

View 2 Replies View Related

Formatting / Display - Field Not Display 0 If It Is Empty On WHITE Sheet

Mar 14, 2013

I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.

I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).

To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.

This also includes the WHITE sheet.

View 3 Replies View Related

Field Matching Formula

May 4, 2009

I browsed through the forum looking to find a way to solve my problem, but could not find it. Maybe someone here can help me out.

I have lots of data, and it would be impractical to do it manually. Here is an example: ...

View 11 Replies View Related

Formula To SUM For A Given Field Type Specified In Another Column

Oct 27, 2009

I'm sure this will be a very simple one for someone - it leaves me *head scratching* though! I have attached a VERY generic example and it includes what I am trying to SUM for the given data. From the example if we look at Question Z1:
"Total Cost of All types of "Lemons"" = ?? I could simply do

View 5 Replies View Related

Can Use Conditional Formatting To Check If Field Has Value Or Formula

May 7, 2012

Can I use conditional formatting to check if a field has a value or a formula?

View 4 Replies View Related

Formula Which Returns Horizontal Field Names?

Mar 30, 2014

I am having trouble developing a formula which will return a field (specific cells back to me) based on some conditions.

Basically I want a formula which will return the date (G3-T3) to me on each client. On the conditions of there usage.

So I want it to extract the value of the first number to comes across (the value of the number does not matter).

E.g. Gissle (D5) "The formula should go along and provide me with the date (value) "1/7/2013" [Cell I3) in cell [E5).

It just needs to hit the first value it comes across horizontally and provide me with the date (field) which it is located in Column E (E5-E11).

save at work because I had to manually scope out the first value and insert and copy the date (took an hour).

Sheet1 *DEFGHIJKLMNOPQRST2***Usage*************3*

View 4 Replies View Related

Inserting A Formula In PIVOT Table Field.

Aug 27, 2008

inserting a formula in PIVOT table field....

View 9 Replies View Related

Count Formula For Description Text Field

Sep 25, 2006

I import a database with mostly unique records which has a description field that includes, among other things: Model Name, followed by predominant color. Unfortunately, there are no seperate fields for the Model Name or Color, e.g.:


Is there a way to create a summary table which would count the number of occurences of the Model Name and the number of occurences of the color?

Fortunately, there are no more than 25 Model Names, and no more than 10 colors or color/combinations, so we could compare against two lists.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved