I've got the following formula in a column: =IF(C10/B10>=1,20,IF(ISERR(C10/B10),0,0)). The purpose is to ensure that only one of two values (20 or 0) is displayed, regardless of circumstances that regularly generate the # DIV/0 error. The if(iserr(...)) part works fine when tested on its own, but when nested, it's still allowing the error to display.
View 3 RepliesLooking for any assistance: Excel 2000 (9.0.6923 SP-3) Windows XP Pro SP-2
I am trying to get Excel to sort through data in various cells,(text and numerical) and do comparisons with a positive/negative or yes/no error displayed at the end of each row.Suggestions so far range from don't know to use Arrays, I am swamped in MSN help pages and am completely lost now. here goes:
In the example below I want Excel to compare Column P4 against Column M4 and display a result as above, so for instance British Midland (M4) will always be 40100350(P4) & Heathrow Express will always be 40100355, British Rail (Tickets) will always be 40100355,so I want Excel to compare all Suppliers against their corresponding codes for each row Eg (P4 to compare to M4) and display if this is correct or not, the codes 40100350,355 etc do not change ....
I have the following formula which consumes forward demand against an initial stock figures, it nests the next 7 buckets and then finally takes an average of the next 13 buckets to give a weeks forward cover value, unfortunately if the next 13 values are 0 it returns DIV value, how do I nest an ISERR function to replace DIV with 0 ? Thanks in anticipation as I am pulling my hair out, whats left! alternatively a formula to consume demand past 7 buckets?
IF(M40
After searching this board, I am using a check for this issue to alert when the problem occurs, but I'm hoping to tell Excel to fix the problem itself. The issue occurs when a set of data equals 100%, but when fewer decimals are displayed, the displayed total equals 101% or 99%.
Example:
0.6%
1.6%
97.8%
(Now when the display is changed to no decimal points)
1%
2%
98%
As you can see, the data appears to total 101%, and my boss is being a (insert any comment you probably have about your boss) about me not manually reviewing every single report that goes out for this 1% difference. As there are probably 400 different points at which this situation can occur on a daily basis, working harder is not the solution. /rant
I thought I once heard about a formula or setting where Excel will only calculate the displayed values instead of the actual value or formula. Ideally, the formula would know to take the three values, figure out the 100% limitation, and round the largest one down so the other two can round up. The example above is probably 99% applicable, as we would be rounding the largest value down in order to round the other values up.
Meaning intended result:
1%
2%
97%
I have a pivot table that summarises data from a timesheet. The summary looks something like the following and shows the number of 15 minutes intervals spent on a range of categories and jobs:
PivotTableSample.PNG
How can I have this pivot table display the number of minutes instead of the number of intervals (i.e. it needs to take the value displayed currently and multiple it by 15). I'm sure there must be some simple way to achieve this but can't figure it out (I don't want to use vba).
Is it possible to dynamically change how values are displayed in charts?
I have a list which references different named ranges. It either needs to display the values in a graph as a number (without decimals) or a percentage depending on the source. Is this possible? Maybe with some VBA? Or am I asking too much from excel?
I'm currently working on a test matrix that has high level and mid level test cases. The High level results cell is tracking all mid level test cases below it. If all mid level test cases are displayed as "Pass" then the High level test cases displays "PASS". If any of the mid level test cases is not displayed as "PASS" then the High level test case result is displayed as "FAIL" However, "PASS" and "FAIL" do not accurately represent the status of the test cases in the instance of them not being filled out. Mid Level test cases start off in the "Select Result" state which basically says it that test has not been performed. I would like to have a third value displayed as "NOT COMPLETED" or "IN PROGRESS". Below is the current formula that I am using.
=IF(AND(C50="Pass",C51="Pass", C52="Pass", C53="Pass", C54="Pass", C55="Pass", C56="Pass"),"PASS","FAIL")
On thing to keep in mind that may or may not make a difference is that one of the High level test cases has 30 mid level test cases and from my understanding, IF / AND function only allows up to 30 logic inputs (not sure if that is the correct terminology or correct at all).
I have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this?
As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.
I am setting up a summary sheet that contains =indirect() functions for workbooks that don't exist yet.
I would like some kind of function that returns the =indirect() function correctly if the workbook exists and just a 0 or blank if the workbook doesn't exist.
I would like to have the indirect function in all of cells that as soon as someone creates a workbook it will update the summary sheet.
I am using an area of 43 Rows by ~ 150 Columns to display the results of an advancedfilter process. I have a trigger on row 44 for each column (which is either 1 or 0, depending on if any of the rows in the column were filled with data. If any column is completely empty I would like to hide it. I am getting an error if the majority of the ~ 150 columns are empty and should be hidden. Does anyone know if there is a maximum number of columns that can be hidden each time a macro is run?
My piece of code to accomplish this is as follows:
For i = 4 To 150
If Worksheets("RegMatrix"). Cells(44, i) = 1 Then
Worksheets("RegMatrix").Columns(i).AutoFit
Else
Worksheets("RegMatrix").Columns(i).ColumnWidth = 1
Worksheets("RegMatrix").Columns(i).Hidden = True
On Error Resume Next
End If
Next
Right now, once i gets to about 100 the rest of the columns are not hidden.
I am trying to hide four columns on my worksheet, but for some strange reason I am getting the following message:
"Run Time Error 1004
Unable to Set The Hidden Property of the Range Class"
I have no merged cells or objects in those columns. Has anyone else suffered this problem?
I am working on creating a spreadsheet that can be updated by those unfamiliar to excel. I have a master list on one sheet and three separate lists for business, individual, and general. Each list has three columns giving name, address and postal code. There are numerous repeats on these lists and I wanted to hide any duplicates while keeping my cells aligned. So far I have been able to highlight any duplicates, but was interested in some function that would save at least one duplicate while hiding any additional ones.
View 2 Replies View RelatedI pieced together this code last night but I cannot seem to get the hide row function to work correctly. Any ideas on how I can correct the hiding rows portion? I just want the code to hide the entire row if columns K:Z are all blank. So I assumed I can make the CountBlanks=16, then hide that row if its true! Thanks to Sixth Sense and Norrie for that idea!
Code: ....
I'm relatively new to this, and its entirely possibly (more likely probable) that I am attempting to this in the wrong way, but here's what I'm dealing with...
View 8 Replies View RelatedI'm trying to run this macro and I keep getting the "Run-time error '1004' Application-defined or object-defined error"
I am just trying to hide the rows that do not contain anything in them with this
x = 2
Sheets("sheet2").select
If Cells(x,1).value = "" Then
Rows("x:x").select
Selection.EntireRow.Hidden = True
Else
x = x +1
End if
The line that is giving me a problem is "Rows("x:x").select". but when i change x to an actual number, it works. I cannot have a definitive number because where there is data will vary every time this is ran.
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies View RelatedI'm trying to hide all but one duplicate value in a list; I have a report that can give multiple results for each record, but to make it cleaner to read I want to hide the duplicate values, such as customer names etc. after they have first appeared. I'm aware this may not be a very clear explanation so have attached an example spreadsheet.
The left-hand list is similar to what I would start with and the right-hand list is what I am looking to achieve. Conditional formatting will format duplicates but will obviously all to all and can't use the remove duplicates function as it will take away the unique values alongside them in the row. Preferably I'm after a solution that works in both '03 and '07 but it's not a disaster if I can only do it in '07.
I have 3 columns on my spreadsheet with 100 rows:
- each cell in the 3rd column has an equation in it as such:
=h4+f5-g5 which means that the current cell in column 3 is adding the value of the cell just above it + and - the values of the cells on the left to it. This works fine, the only problem is that even though nothing is entered in row 40 for example, there still is a value based on the last calculation in row 5 for example - this value shows all the way down to row 100. Anyways,
How can I hide those values in column 3 all the way down to row 100 if nothing is entered in the other 2 rows?
I have the following data on a spreadsheet created by a formula:
A1: 1.1234
A2. 2.234
A3: 3.34
I have the data formatted to only have two decimal places so it looks like this:
A1: 1.12
A2: 2.23
A3: 3.34
I'm using the following formula in cell B1 to display the values of those cells in a single cell in order A1,A2,A3:
=A1 & "," & A2 & "," & A3
However when the values of A1, A2 and A3 appear in the in cell B1 they appear as: 1.1234,2.234,3.34
Is there a way I can edit the formula or format something so the values in B1 appear as with only two decimal places? i.e 1.12,2.23,3.34
I'm using the following code to hide rows with zero values in my workbook. I have 10 sheets for potential data/products and a "summary" sheet that is set up to pull all the information from the 10 sheets. I often don't use all 10 sheets so there are a lot of blank cells. I set up the code below to hide zero values to make the "summary" sheet easier to work with. I am encountering a problem with cells that are formatted as dates. a zero value/empty cell shows up as 1/0/1900 and is therefore not auto hidden.
Either any adjustment to my formatting or code to automatically hide these blank date cells on the "summary" sheet?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
For Each c In Range("C3:C8778")
If c.Value = "0" Then
c.EntireRow.Hidden = True
[Code] ...........
I'm using the below macros to hide or unhide rows based off of a value in column A. Column A contains an If statement that looks for a value in column F, and depending on whether the corresponding cell in column F contains a value the if statement returns either True or NO-IF(F>0,True,"No).
It currently takes quite a bit of time to run this macro. Given the specs on this machine, I'm convinced that there is probably some loop or inefficiency in my code.
Sub Button1_Click()
Dim cell As Range
For Each cell In Range("a59:A1472")
[Code]....
What I need to do is hide the value in an individual cell, dependent on whether a value (any value) has been entered in another cell. I know this must be possible somewhere in conditional formatting but I can't seem to figure it out no matter how much I try!
The table below should hopefully explain exactly what I'm after. I want to hide the value in the balance column (automatically calculated)when no transaction has taken place (ie, there is no date entered in the date column). At the moment this value appears all the time.
DATE
DETAILS
DEBIT
CREDIT
[Code].....
I've tried several approaches on how to code this, but can't figure out how to work it. I have a large spreadsheet; which dynamically changes in both column and row counts. So, I have an array of values. If any cell has a negative value, that cell text needs to be changed to red AND that row needs to remain displayed. If ALL values in that row are >=0 then the row can be hidden.
The array is actually a pivot table named QTD
For Example: If cell D5 = 5 and F5 = -3 then Row 5 should remain displayed.
If Cell D6 = 5 and F6=0 then row 6 should be hidden because all values are > 0
Then I was thinking of using the case statement to hide the rows but can't figure the syntax. I'm open to any other means of performing the taks as long as the initial array dynamically to encompass all the data.
I need to hide the rows in an excel whose value is equal to the array list that has been already hardcoded...How to do this...
View 1 Replies View RelatedI am currently building a tool that works with an undefined range of input variables, e.g. I now have a range of 200 values but it could just as easily be 400 or 100. I am using these values for further calculations and have thus 'dragged down' to cells that in some instances are empty, resulting in values that are 0. I'm also building a chart based on these values, but it shows the values that are 0 as well!
Is there any way to just plot the non-zero values without changing the data range?
I have a sheet with cells that will vary and change in value. The far right column (in this case G) is calculating a percentage from cells in columns D and F. At the moment only 9 rows are being used but I have made provision for the list to extend down to row 100 for future use. Cells in columns D and F are receiving their values from sumif formulas on another sheet, therefore a lot of cells are showing a 0 value for the moment (which I have formatted to show blank).
My problem is that I have cells in column G (percent formula) which are of course showing #DIV/0! due to the fact that they are calculating cells with 0 value. How can I format the cells in column G to show blank until they receive a real calculation!
Does Excel have the ability to automatically hide rows without values in certain cells?
For example:
Sheet1
ABC1Route NumberTechnicianNumber of Units Cleaned25Tony237Don749 511Ray12613James16715Chuck21817
In this example, Rows 4 and 8 would automatically be hidden, leaving the other rows displaying. Of course, somehow I'd have to "Unhide" these rows at some point to add data if needed.
This is for a spreadsheet that is about 500 rows. Conceptually, I would automatically hide the rows w/o data in column B, analyze (or print) the worksheet, then "Unhide" the columns to enter data the next day.
the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.
Q Header
R Header
S Header
50
50
50
[Code]....
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
I'm trying to hide all columns which have the word "hide" in row 6. I have done a similar thing whereby I hide all rows which have the word "hide" in column 3 using the following
Sub HURows()
BeginRow = 9
EndRow = 40
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Alas changing the number and every Col for Row and vice versa doesn't work!! Really I only need to search colums G to U inclusive. The code must also unhide colums if the values in the cells of row 6 change to anything other than "hide".