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