# Referenced Cells Returning Zeros: Can These Be Made Blank Or String Data?

Aug 30, 2007

I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.

If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.

Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?

## How To Get Referenced Cells Returning Blank Cell Instead Of Zero

Apr 6, 2014

Referenced cells returning zeros: can these be made blank or string data?

But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?

My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!\$D\$6:\$K\$100,3,FALSE)

## Ignore Blank Cells Zeros And Error Cells From MIN Function?

Oct 24, 2013

I have a spreadsheet for which I have to set up a formula to get the minimum value from a range of cells, but that range can include blank cells, errors (#DIV/0) and zeros, all of which I want to be ignored. I can work out how to ignore EITHER the zeros

(=MIN(IF(C10:G100,C10:G10)),

or the error cells

(=MIN(IF(ISNUMBER(C9:G9),C9:G9)),

How to exclude both. If I try to combine both of these exclusion criteria it doesn't work and I end up with the answer #DIV/0, which is one of the values I want it to ignore.

## Leave Cells Blank Without Zeros

Sep 19, 2009

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

## Suppress Zeros In A Pivot Table (search Out Any Zeros And Replace With A Blank Cell)

Oct 12, 2009

with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.

What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.

Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.

## Chart Omitting Blank Cells & Zeros

Feb 21, 2008

Here is a question about the chart in excel. I have a sort of data where only part of the data is in numbers, and when drawing the chart, the whole range of data were selected. Then on the chart, there is a line drop to the x- array, anyone knows how to get rid of it? It is a line chart. Here's an example if i did not clearly describe the problem

Here's data
X Y
4354.28
5354.75
6352
7343.82
8aa
9aa
10aa
11aa
12aa

and between 7 and 8 on x, the line will drop from 343.82 to 0, and i don't want this part, i want the chart to show to 7 and leave the 8 to 12 blank..

## SumIF Returning #VALUE! When Referenced Workbook Is Closed

Oct 9, 2009

My SUMIF function only works when the workbook that I'm referencing to is open. When it's closed and I open the file it shows #VALUE!. How do I fix this problem?

## Returning String From One Cell Based Upon Multiple Cells

May 22, 2013

I need to return the specific text of a cell based upon the text in multiple cells.

Worksheet as follows:

Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*

[Code].....

I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.

If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".

My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.

## Excel Web Query Is Returning Blank Data?

Jun 23, 2013

I am trying to get data in excel sheet using web query from the following web link:

But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.

## Access Table Is Returning Back Zeros

Jun 23, 2008

The code below (ran from Excel vba) deletes all data from an Access table, paste data from Excel into another table, and then run two Access Macro's and paste data from yet another table back into Excel.

My problem is that when I run the query from Excel, the data returned to Excel is always zero. When I step through the code, and check the Access database, all the steps are carried out properly. E.g, the delete query is deleting all the records in the correct table, the append query is copying the correct data into the correct table in Access. The two macros do run, but then when the resulting table is returned to Excel, it brings back zero's.

Doing all this manually in Access is returning the data correctly.

The two Access macros are running some queries, E.g. the "001 UpdateLatestData" is running two queries and the "002 CreateMonthlyResults" is running about 15 separte queries.

Sub OPENACCESSTABLE_DELETE_ROWS1()
Dim db As Object
Dim accApp As Object

Access_DB = ActiveWorkbook.Worksheets("MACRO").Range("C1").Value
DBPath = "C:MinetteProperty.mdb"

Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase DBPath
'Delete Query
DoCmd.RunSQL "DELETE [001 non motor data].* FROM [001 non motor data]"

Set accApp = GetObject(Access_DB) .........................

## Count Number Of Blank Cells In Range Starting And Ending Will Cells That Match String Values

May 13, 2013

I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.

I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?

The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.

## How To Delete All Zeros In A String

Dec 11, 2007

I have data (strings) which looks like this:

000000000001000
000000000392000
000000000003000

no I want to write a routine which delets all zeros from left to right untill the first "normal" number appears.

Result:

1000
392000
3000

## Adding Two To Six Zeros To The End Of String Of Numbers

Jan 11, 2013

I need to add 2 to 6 zeros to the end of string of numbers beginning in A2:

01
011
0111
0112
011901

The end result, all numbers must be 8 characters long.

## Remove Letters And Zeros From String

Oct 20, 2011

I have a list of about 1,400 numbers ranging in different lengths. What I'm trying to accomplish with a formula is to remove the single letter at the end of each number (not all numbers have them) and in addition to removing all zeros at the beginning of the number (again not all numbers have them).

Here is an example...say I have the following 5 numbers:

8014554
45678456
87451245
0008014554b
0008014554c

And what I need my formula to do provide is the following results instead:

8014554
45678456
87451245
8014554
8014554

So far I have started with the following =LEFT(A1,LEN(A1)-1), but that will always remove the last character (number and letter).

## Imported Data Creates Blank Cells That Aren't Really Blank

Feb 21, 2006

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C.

## Display Leading Zeros In Character String?

Feb 18, 2013

I have a code which displays the values of R2, S2, T2, and U2 in one cell (J2). R2, S2 and U2 are all letters, but T2 is a 2 digit number. If there is a leading zero in T2, then in the display of J2, the leading zero is gone. so if the value of R2 is K, S2 is M, T2 is 01 and U2 is S, then I would want it to display KM01S, but it is only displaying KM1S.

## Adding Zeros To The Beginning Of String Of Numbers

Aug 12, 2013

I need to add zeros to the beginning of string of numbers in column A (given below), for e.g., 000029, 002562, 015096:

A
29
2562
15096
286
1

## How Can I Replace Zeros With Blank Spaces During Calculations

Nov 19, 2005

I am trying to get excel to sum totals, but exclude answers that are outside
of a certain range. I can get it to do this by using =IF(Z8>0.5,Z8,0).
However, I need it to display absolutely nothing in the cells, rather than a
zero because it alters future calculations.

## Zeros Not Showing, The Result Is A Blank Cell

Aug 8, 2007

When i put a "0" in a currency formatted cell, the result is a blank cell. If i put any other number in there other than "0" it shows a dollar amount of that value (even .00001 works). I have also noticed that before i put a zero in that cell, i can go to the format of that cell, select currency, and i see a sample in the sample box (\$0.00). But if i go to that cell, put a "0" in it and return to the format of the cell, the sample box is now blank and it doesn't even show a \$ sign. I know the "0" is in the cell because i can see it in the formula bar. But on the sheet itself it does not display it.

## Referenced Value In A Cells In 5 Rows

Mar 6, 2006

I have a vlaue referenced from a sheet 2, and I need to put this value (Same value) in a cell, say O3,and then O4, O5... six times counting from O3. i.e O3=150, O4=150, O5=150....

150 is coming fr4om "Worksheets("Interest Calculations").Range("F4")"

For x = 1 To 6
Range("O3").Value = Worksheets("Interest Calculations").Range("F4")
Next x

## Having A Macro Run If Changes Are Made To Certain Cells

Mar 31, 2009

Is it possible to write a macro that will run if a user enters data into any cell in the worksheet that has a formula in it.

## Sorting Cells With Referenced Names In Name Box

Jun 8, 2009

I'm trying to do is sort cells which are referenced by names in the name box when a button is clicked. All the coding is in VBA.

If a cell is sorted by a macro, I would like the referenced name to move with it on the spreadsheet.

For example if the referenced name box for C5 is Bob and Andrew is the referenced name box in C6, when a button is clicked I would like the Andrew reference to move to C5 and Bob to move to C6. This is sorting in alphabetical order. However, I would like the referenced names to stay the same. Referenced Name refers to the cell name box.

Is there a way to do this in VBA?

## SUM Formula Not Summing All Referenced Cells

Sep 5, 2006

My problem is that when i used the forumla = SUM(Q11:AW11) to add up the range of cells it dosnt do it. It only added up 2 of the numbers in the range of cells. And missed out everything else. This forumla is being used to add up all the volunteer hours for volunteers for each month and for all the other months the formula worked fine but now its just being a pain.

## Delete Cells When Any Changes Have Been Made To Sheet?

Jun 27, 2014

I've tried for a while on my own but no luck. I'm looking to delete cells D21 and 22 when any changes are made to the sheet.

I've tried using Private Sub Worksheet_Change(ByVal Target As Range) but no luck.

## Replacing Referenced Cells With Static Text?

Feb 26, 2013

I'm making a vba macro that takes a workbook and splits and saves it as individual sheets. In this process I need to find all the referenced cells (ie =E5) and change them to their text or numeric value. A few things I've found on the form and tired with no success:

Selection is a column selected from the top to the last used cell (I know there is only 4 columns that have references)

VB:
Selection.Copy
Selection.PasteSpecial Paste = xlPasteValues

Also tried:

VB:
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value

I feel like one of these should be working. But even after the paste the values are still references.

Edit: An afterthought, if I were to select the whole sheet and do a copy/special paste would it replace all formula cells with their calculated value?

## How To Hide Displayed 0 In Two Circular Referenced Cells

Feb 3, 2014

Very simply, if A1=B1 and B1=A1 - how do I trick excel into displaying a blank cell for the purpose of printing pages?

Further information:

I am working on an evidence log, it's just a spreadsheet with columns for collection info; all of the information put into the log on one sheet, is referenced on a second sheet where the info is arranged in a printable grid to make sample tags.

For some ungodly reason, I have been instructed to make all of the references circular, so that anyone with a particularly masochistic streak can log their information on the tag to populate the log and vice verse

This results in my log and tags each having 0 values displayed in all of the cells, which I didn't think was a problem, until it was pointed out to me that people may want to print out sheets and use them to take notes in field, making blanking the cells my ultimate goal.

## Have Cell Allow (Range) Or Multiple Cells To Be Referenced

Mar 14, 2014

Trying to get a problem solved to have a % discount & 'flat' \$ discount apply to cell(s) referenced. Product A, B & C are available in 3 different materials. Objective is to allow each material's Max Discount column to apply discount(s) referenced in cell J3 & K3, but only in the order of applying the percentage discount BEFORE the 'flat' \$ discount.

Formula in cell C3 works IF the cell "Range to apply discount" I3 = "B3". Works just fine if I allow only one cell to be displayed in I3, but ideally, I'd want this to allow the said discount(s) in cell J3 & K3 to apply to any cells mentioned (for instance, for Product A Material 1, Product B Material 2).

I had tried a drop-down menu using Data Validation, which in my actual project allows me to select ONE cell at a time (out of my list of options), which is okay, but it would be extra useful to have a checkbox option to select which ones to apply the same discount to, instead of creating a massive embedded IF function to have each product in each material find whether it's cell is referenced in 20 different places (if I just copy the already functioning single cell reference tool).

The scale in which I'd like to use this in would be to apply specific discounts for one product, but a different discount for another product or material, and allow the input cell I3 to include a checkbox drop-down option (like the filter/sort), but not remove the data in the table (Range A2:G5 in this example), so I can hide any unnecessary columns and print, showing certain products & materials having a 10% discount, while others having a 20% discount, with an additional \$5 off, etc.

[URL] ........

## How Can A VBA Macro Find Which Cells Are Being Referenced In A Formula

Jan 15, 2008

I have a spreadsheet with a number of different formulae on it. I have a macro which I want to extract which cells are referenced in the formula in the active cell and then branch based on the which cell is referenced.

Is there a way to find the referenced cells without parsing the formula string?

## Comparing Customer Names In Rows And Returning Text Data In Different Cells If Same

Jan 26, 2010

I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:

Sheet 1

A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase

I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:

Sheet 2

A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes

Does this involve IF, MATCH or VLOOKUP formulas?

## Returning 0 Instead Of Blank

Oct 16, 2012

Below formula matches two criteria

=SUMPRODUCT(('[Value.xlsx]test!\$B\$5:\$B\$50=\$B5)*('[Value.xlsx]test'!\$C\$5:\$C\$50=\$C5)*('[Value,xlsx]test'!D\$5:D\$50)).

What it does is:

Matching B5 value in range B5:B10 of tab "test" in worksheet "Value"

Matching C5 value in range C5:C10 of tab "test" in worksheet "Value"

AND capturing the matching value in range D5:D50.

The problem is that i am getting 0 if the matching value is a blank or 0.

I want the formula to return nothing (i.e. blank) when the matching value is blank and 0 only when the matching value is actually 0.