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.
View 5 Replies
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.
When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.
And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.
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 don’t hide the row
If the value is “0” or “ “ then hide the row
Sheet2
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet3
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range O1:O150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range B1:B150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
I'm stuck using Excel 2003 to auto-populate a cell.
I have a range of dates in five consecutive columns called:
Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5
I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.
Each phase is consecutive to the next so will always be filled in from 1 to 5.
I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.
I've tried nesting some ISBLANK functions without any luck.
Is there a way to hide a group of cells based on the value of a cell in another sheet?
I have attached my spreadsheet.
So, based on the selection in H5 on the 'Wood Selection' sheet, I would like to hide cells on the 'Moisture Controlled Schedule' sheet, and the 'Time Controlled Schedule' sheet.
If Ponderosa Pine is selected, I want it to hide all the douglas-fir info, and vice versa
I have a cell with the following formula: =IF(ISERROR(AVERAGE(A2,C2,D2)),"",(AVERAGE(A2,C2,D2)). I'm using this formula because I want to average the selected cells, but if A2,C2, or D2 are blank I don't want it to return the # DIV/0! error, hence the "ISERROR" part.
Now... I want to conditionally format the cell with the above formula in it so that if it is equal to or greater than 80% it's green. Here's the problem, the conditional format colors the cell green if it is blank too. How do I stop that?
Below is an attached spreadsheet to show what I'm trying to do.
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 have three cells where a user will input data, in some cases (2T Weld Condition) they will only enter in B12 and C12, but in the case of a 3T weld they will also enter data in the D12 cell. I then use a formula to check for the thinnest material and that is entered into another cell with a formula, B14. I then need to check the value in B14 to verify if it is above zero, but below 0.65 (mm). If it is then I would like to have a message appear on the screen notifying the user that they are outside the acceptable range.
I cannot figure out how to use the information in cell B14 because it is a formula and my code only works with a direct value. The code I am using works if I point to one of the three input cells, B12, C12 or D12. How do I use the information in B14 to work with the code below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$12" Then
If Target.Value < 0.65 Then
Run "MyMacro"
End If
End If
End Sub
MyMacro loads a userform with buttons, etc.
macro below that will only hide columns if all the rows (e.g. row 8 to 18) have no value?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("U18:AB18")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
[Code].....
Is there anyway to have columns automatically be hidden based upon a cell value? I have a column for each month, and I would only like to show months that have occured, and keep future months hidden from view. I have the current month end date in cell a1 and the following columns each have a date as the header?
View 2 Replies View RelatedMacro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies View RelatedI am attempting to hide a series of rows based on if the cells in that row are blank. The catch is that the field of data in the column may vary as follows:
D E F G
x x X x
x X x
x
I would like to eliminate all the rows past the last X value in Column D for example
Below is the code I am attempting to use
Rows("41:60").Select
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
[Code].....
I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Means, if the sum of all the cell (except first and second cells in that row) in a row is zero, then that row should be hidden.
View 9 Replies View RelatedI want to hide a sheet in an excel workbook based on a Yes or No answer in the first sheet in the file. Is there an easy way to do this?
The above operation will be repeated up to 10 times, but the decision to hide or not hide each sheet will depend on only one answer.
In some cases I want to hide several sheets based on one answer.
The overall objective is to have a flexibly sized workbook suitable for a range of users who will not be faced with sheets which are not relevant for their individual circumstances.
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time.
If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs)
If done at 0830 hrs, Shift 2 (0801 to 1600hrs)
If done at 2030 hrs, Shift 3 (1601 to 2359 hrs)
I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View 4 Replies View RelatedI'm trying to work out the way of having a cell give a result based on what has been input into two other cells.
Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
My company has a canned template for some of the work we do and to avoid wasting too much paper I wanted to insert some extra code into an already programmed macro button (which sets the page breaks) to hide forms (both as individual worksheets and rows within separate worksheets) if the field that ought to auto-fill them is left empty.
View 3 Replies View RelatedI am very new to VBA. I need to have a code that will hide/unhide cells based on a date range.
Cell A1 contains the formula for todays date, based on that answer, I want to hide/unhide columns based on a weekly date range.
Ex.:
Today is 03/18/2014 I want excel to say If A1(Today) is greater than 03/14/2014 and less than or equal to 03/21/2014 then unhide column E and hide the rest.
Then let's say Today is 03/22/2014. I want excel to say If A1(Today) is greater than 03/21/2014 and less than or equal to 03/28/2014 then unhide column F and hide the rest.
columns in questions are D-AC
way to automatically hide a row or column based on a specified cell.
Or even something general like: Hide all rows with cells evaluating to #n/a.
I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F
For example:
Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7
I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]
Here are an example of what I have tried, amongst many...
=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
I have a combobox that returns me the names that are in a spreadsheet.
I need a return label, the index (address of that cell that the combobox returned), how do I do that?
how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.
View 2 Replies View RelatedI 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 a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia
What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want
1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C.
2. Basis the range I want sheets to show or hide.
I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.
IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column
IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column
IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................
