I have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" And Target.Value = 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True
Else
If Target.Address = "$C$7" And Target.Value <> 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False
End If
End If
End Sub
Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.
I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.
We receive payments in either USD or HNL. I want to be able to have a final price (last column) that puts all prices in USD. I have a reference cell on a separate worksheet (drop down inputs) that we will use to store the data for validations and the conversion rate. We use one conversion rate for all transactions for a fiscal year, so only need to update once a year. Not sure how to make this work.
I would like to gray out or hide contents of a series or group of cells when a particular cell has a certain result. i.e. if cell A1 is <5 I want cell rows 5 thru 10 to be hidden or grayed out.
Excel 2003. I have been struggling for an hour how to hide a number to text macro in a way that only macro result is displayed in a cell.
(I have one 2-3 years old xls, where I have managed with task, but now can't figure out how and how to unhide the macro Can it be password protected somehow?
I have Cell C63 in Worksheet 1 which is set up to say either "YES" or "NO" depending on whether a value on a different worksheet exceeds a value in an adjacent cell or not (also on that worksheet). The formula I am using is simply:
Essentially I don't want anything displayed if there are no data in the range C4:C54 on Worksheet 1. I thought I need a nested IF function but I couldn't get this to work.
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns and i need the macro to be fast
Sheet1 If the value in Range BE11:BE160 equals 1 dont hide the row If the value is 0 or then hide the row Sheet2 If the value in Range BE11:BE160 equals 1 dont hide the row if the value is 0 or then hide the row Sheet3 If the value in Range BE11:BE160 equals 1 dont hide the row if the value is 0 or then hide the row Sheet4 If the value in Range O1:O150 equals 1 dont hide the row if the value is 0 or then hide the row Sheet4 If the value in Range B1:B150 equals 1 dont hide the row if the value is 0 or then hide the row
I got an excel, with a "validation list" in a certain cell.
Users can select value1, value2 or value3. based on this value, some columns will have to unhide (standard= hidden) (only for value2 this is the case).
I used the worksheet_change event to determin the value, but so far I'm only getting it to hide...
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If C24 = "value2" Then Columns("H:O").Hidden = False Else Columns("H:O").Hidden = True End If End Sub
I am creating a copy of a spreadsheet (table format) in a separate workbook using formulas that will update the copy as the original updates. I would like to auto hide the rows that have the value "Returned" in column G. I have columns A - G , rows 2 - 2000. The value "Returned" is the result of a simple = formula. Is there some way to accomplish this?
I've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.
I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.
I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.
I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = 1 Then
Rows(Target.Row).RowHeight = 0
Else
End If
End Sub
Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?
Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?
Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.
I started the formula in P2 of the GL Detail-2012 tab. File attached.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
If Sheet1.Range("A34:A94") = "HIDE" Then For Each cell In Range("A27:A94") If UCase(cell.Value) = "HIDE" Then cell.EntireRow.Hidden = True End If End Sub
How to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year 2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks) 3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM 4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
I have a formula sheet that uses an IF statement to determine if one columns data is bigger than another. Out of the 300 or so rows there are approx 20 that come back as yes (this is in column A) and the rest are blank
What I would like to do, is for the 20 or so rows, I would like to pull out (copy) columns B, J and L and put them into worksheet 2. Preferably without any gaps in the rows or columns.
I hope this is enough information, I am using MS excel version 2010 although I think the people who will ultimately be using it are on an earlier version.
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required" if B5 is +3% then b6 =110 if B5 is +2% then b6 =106 if B5 is +1% then b6 =103 if B5 is 0% then b6 =100 if B5 is -1% then b6 =96 if B5 is -2% then b6 =93 if B5 is -3% then b6 =90 if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
I have set of column data A & B In those A contains Serial No & B contains Ticket No...
I am looking for formula to solve the function by those conditions... almost get done by countifs functions.
1.If Serial & Ticket No Only Once result should be "FIRST TIME LABOUR ONLY" 2.If Repeat Twice For The First Serial No And Ticket "Labour Only", For The Second Serial No And Ticket "Labour & Parts" 3.If Serial No Twice But Ticekt No is different for both serial no Result Should be "PART USED 1 OF 2" 4.More Than 2 Times Serial & Ticket No Repeats" Result Should Be "CCI"
I have a list that is streets and addresses. All contained in column A. Cell A1 is the street name and then Column A3 is the street number. This repeats down column A for almost 1000 street names. I need to fill column B3 with the street name, as well as B4, B5, B6, etc until the street name changes. I was trying to do this with an if..then but couldn't get it to work. I also tried to work on a do.. loop looking for the change from a string to number. But my programming is a little rusty. If anyone can help I was be forever grateful. I mean the alternative is to sit here and copy and paste all day.
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event 2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement 3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible 4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean Set d = Intersect(Range("I:I"), Target) If d Is Nothing Then Exit Sub For Each c In d Select Case UCase(c) Case "C" fc = 1: fb = True: bc = 4 Case "O" fc = 2: fb = True: bc = 3 Case "D" fc = 2: fb = True: bc = 46 Case "G" fc = 2: fb = True: bc = 5..................
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A 0 0 0 0 0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
I have a Worksheet were I have a formula in cells from D5 to ALO5. That read "Hide" or "Unhide".
I would like the column to each hide individually hide if that column has "Hide" in in it. If it has "Unhide" in the column then it becomes visible again. The range of the columns match the formula cells which are "D" through "ALO".
Is there any formula to hide a column based on a value . I know we can do that manually using hide and Unhide option. but I want this to be done dynamically without A MACRO.
In the attached sample spreadsheet, the row 3 (status) has either 0 or 1 that is feed from another sheet and change dynamically. I need to hide all columns with a value 0. In this sample columns B,D,H,I,J,N & P must be hidden.Suppose If the staus value is changed from 0 to 1 then the column must be displayed (unhide).
I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell? There is only one column of data.
