Show Rows Based On Data Value?
Mar 13, 2014
I want to show/hide multiple rows based on the value of a cell on another sheet.
For example,
If Sheet1!A1="A" I want to show hide rows on sheet 2 as follows:
2 Show
3 Show
4 Hide
5 Hide
6 Hide
7 Hide
If Sheet1!A1="B" I want to show hide rows on sheet 2 as follows:
2 Hide
3 Hide
4 Show
5 Show
6 Hide
7 Hide
If Sheet1!A1="C" I want to show hide rows on sheet 2 as follows:
2 Hide
3 Hide
4 Hide
5 Hide
6 Show
7 Show
In my actual example the number of rows to show hide is greater (27 rows per selection) so a range, rather than specifying individual rows would be best.
View 4 Replies
ADVERTISEMENT
Mar 7, 2014
I would like to present the user with an input box asking him to enter a numerical value greater than 0.
Then I would like the code starting with row 10 and consult the vaues in column O, and only display the rows that have number in it less than or equal to the number in the input box. I was hoping it would involve a filter.
I would also need a macro to display all of the rows again.
If this is too hard, how about copying the results to a new worksheet along with the labels in row 9?
View 2 Replies
View Related
Jun 10, 2008
I have two sheets say:
Sheet1
Sheet2
Sheet1 has a few comboboxes saying (YES / NO) conditions Which are assigned to particular cells (for Ex: say Combobox1 value assignes to Sheet1!B5 )
If Sheet!B5 = YES some rows in Sheet2 Say ( Row12 ,Row 15,Row 16) has to be hide.
I will add a command button to sheet1 and call macro if i click command button checking the conditions in sheet1 combo boxes..rows in sheets2 has to hide..
View 4 Replies
View Related
May 7, 2013
Trying to have A1 in Sheet1 having three values 1,2,3 if A1 = 1 hide rows from 1:5 and 10:1500 in three Sheets2,3,4
View 8 Replies
View Related
Jan 29, 2009
I'm trying to create a formula (used in sheet 2 column B) that would generate the results in sheet2.B based on the contents of sheet 1. sheet2.B2 would contain a formula searching for the both "Y" in sheet1.C and the text in sheet2.A2 (in this case "E&P"). When each match is found, the contents of sheet1.A should be returned to sheet2.B, as shown in the mockup. There will be multiple matches (at least 15-20) for each search criteria, and I don't know how many there will be ahead of time.
I've tried various formulas, and they either have incorrect syntax and return every row in a range, or only return the first match correct match each time instead of all correct matches. I found some UDFs posted online that claim to do this, but I've gotten them into the spreadsheet and they generate # NUM errors. I would prefer to work with Excel-native formulas if possible, because I'm going to eventually hand this off to someone else to maintain and they may not have the level of expertise to deal with UDFs.
View 4 Replies
View Related
Feb 27, 2008
I wrote a small code to hide some columns if a certain cell is equal to a certain string.
The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.
Sub Auto_Open()
Range("A1").OnEntry = "Action"
End Sub
Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
View 3 Replies
View Related
Mar 4, 2008
First, I realize there are plenty of hide cells threads but I have yet to find one pertaining to my situation and I apologize in advance if I this solution has already been posted.
The Problem: I am looking for macro code to a toggle button that will hide various rows that have no value between multiple sets of rows. The toggle should refresh the format of the rows as the information that was blank could later on have value.
The Setup: For each set of rows, the first row will have the label T and the last will have the label S. In between T and S there can be data. If the cells between T and S are all empty then the rows including T and S should be hidden other wise only the non blank cells between T and S should be visible.
The Reason: I have a master database worksheet, there are four copies of the master database worksheet each entitled phase1, ""2, ""3, & ""4 respectively. These phase sheets are linked to the master sheet and show the entire row's data based upon the beginning cell of each row showing either phase1, ""2, & so on.. The data is broken up into many sub databases and traditional auto filters or advanced auto filters will not be applicable as the title of the data and the empty rows in between need to be hidden if the data is empty.
View 9 Replies
View Related
Oct 26, 2008
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
View 3 Replies
View Related
Aug 6, 2009
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:
[url]
The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...
View 3 Replies
View Related
Mar 5, 2009
I require a formula that will show unique data in a specific rows or cells. Here are the data:
Raw Data
Col1
Row 1 A
Row 2 A
Row 3 B
Row 4 C
Row 5 C
The result should look like this (Note that the unique data are displayed in rows 6 to 8:
Col1
Row 6 A
Row 7 B
Row 8 C
View 5 Replies
View Related
Dec 2, 2013
I have a spread sheet in which some rows are grouped.
So I can see the + (or -) buttons on the side, and the 1 and 2 buttons on the top, to hide or show the separate grouped rows.
Now, depending on the value of a certain cell, I either want to show or hide the grouped rows.
So actually I want to control the +/- and 1/2 buttons indirectly.
Is this possible? Or should I do this by hiding rows instead of grouping them?
View 2 Replies
View Related
Sep 29, 2006
I have a large workbook that contains markbook info of all my classes that I teach.
I don't want them to see all my workbook as it would be too confusing. I would like to hide all the data and have a simple INPUT box on a separate page that would ask for their name. The name would then be used to find the row number, add this to a specific column number (always the same) which hold their marks and then return the mark in a Message box.
View 2 Replies
View Related
Mar 2, 2007
Call centre data for financial years located in separate workbooks (1 for each year)
Here's what I want to create.
One main file to consolidate/view all data
I've tried to make this as user friendly as possible by having a validated list, whereby the user can select the year they want to view, and hey presto, the data changes to reflect the year displayed in the validated cell.
View 5 Replies
View Related
Feb 11, 2013
How to autopopulate numbers from data sheet based on Selected month.
View 4 Replies
View Related
Nov 30, 2012
I am trying to show a value in a cell, based on the values found in other cells. Essentially, here is what I've got:
If C2 is greater than A2, B2, D2, then put the value found in C1 in E1.
View 9 Replies
View Related
Mar 20, 2009
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
View 9 Replies
View Related
Mar 31, 2014
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
View 3 Replies
View Related
Jan 10, 2005
I have a spreadsheet that queries the web for a weather forecast each day. I'd like to associate pictures on the excel page based on the weather description, but can't figure out a way to select /change the picture automatically.
View 3 Replies
View Related
May 10, 2009
I'm building a spreadsheet for a sporting franchise using a pull-down validation menu. Once the opposing team name is selected from the drop down menu, I would like it to display the opposing teams players and their statistics.
On the database worksheet, I have a web query pulling names and statistics from the web. Each player name has his team name in his corresponding row. What function can I use that will search for that team name and report it back to the display sheet? Once I have the player name there, I think I can figure the statistic portion... But I can't seem to figure out how to pull all of the players with the matching team name into my display sheet.
Example:
A1 on worksheet "A" contains a pull down menu with team names "Lightning", "Storm", "Magic", and "Thunder".
A16-A20 on worksheet "B" contains players from team "Lightning"
A21-A25 on worksheet "B" contains players from team "Storm"
A26-A30 on worksheet "B" contains players from team "Magic"
A31-A35 on worksheet "B" contains players from team "Thunder"
B16-B20 on worksheet "B" contains team name "Lightning"
B21-B25 on worksheet "B" contains team name "Storm"
B26-B30 on worksheet "B" contains team name "Magic"
B31-B35 on worksheet "B" contains team name "Thunder"
If I pull down "Lightning" on A1 (worksheet "A"), I want the following to display:..........................
View 4 Replies
View Related
Jun 26, 2014
formula to use in my report
Let's say I have a summary table on Sheet 1 6000 rows of data on sheet 2.
In Sheet 2 I have 3 columns, Name, Month and Score. Name Column (A) has names of students Month Column (B) has months January, February and so on. Score Column (C) has data from Green, Yellow and Red. Red is if they failed.
In the summary table on Sheet 1, I need to show all the names that got a "Red" Score. And their corresponding months.
It should look like this.
Month l Name l Score
-----------------------------------------
January l Mike J l Red
-----------------------------------------
March l Joe M l Red
View 2 Replies
View Related
Dec 12, 2007
I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:
Sub FilterDates()
Application. ScreenUpdating = False
Sheets("PnL").Select
Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated
View 9 Replies
View Related
May 13, 2009
I want to show which rows of data have been changed by incrementing a revision attribute. For example, if a user changes the contents of a cell anywhere between rows 2 and 13 and col 1 and 9 then the revision attribute in col 10 would increment from 1 to 2 (for the affected row). If another change affects the same row then the rev attribute would increment to 3, and so forth. I don't care which cell was changed only that something on that row was touched.
I thought the CHANGE event was a dead ringer for triggering some VBA code to control this but, since part of the change event code writes the revision value, this triggers another CHANGE event causing an endless loop until something (??) kicks in and stops it after 220 iterations. Is there a way to inhibit the change event just prior to updating the cell containing the version attribute?
Rather than post the code here I have submitted the workbook that includes the whole setup and code. I should also mention I looked at all the other worksheet events and I do not see any "triggers" that would fire each time a cell content is changed. As a side note, is there a way to step into the code of a change event? F8 does nothing.
View 3 Replies
View Related
Feb 22, 2008
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
View 4 Replies
View Related
Sep 8, 2009
I have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11:
=SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39:
=(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
View 9 Replies
View Related
Sep 30, 2008
Attached is a spreadsheet describing my problem in greater detail, but essentially i want to extract rows of data depending on quoter name. I'm fairly competent with excel formulae, but cannot get the desired results. I know nothing of VBA other than copying and pasting other's VBA projects in and fiddling with them until something good happens!!
I wish to extract the data and deposit into another worksheet or better still another workbook entirely but without any breaks in the rows of data.
Havent had much luck with advanced filter either.
View 14 Replies
View Related
Oct 9, 2009
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
View 9 Replies
View Related
Sep 21, 2012
two things on the attached sheet.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
View 6 Replies
View Related
Jul 2, 2014
Every day I collect 2 values. The data is placed in a row, with this scenario:
Day1Val1, Day1Val2, Day2Val1, Day2Val2. etc.
On any specific day, either Val1 or Val2 can be the larger value.
I want 2 rows, with the following scenario:
Row1:
MAX(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
Row2:
MIN(Day1Val1:Day1Val2), MAX(Day2Val1:Day2Val2), etc
This is for a Min/Max (stock) type chart.
Entering the formulas for the first 2 columns and then extending doesn't work because it single steps column numbers instead of double-stepping.
I already have 5 weeks of data, (and 4 rows: 280 cells) so I don't want to painstakingly do it manually.
View 10 Replies
View Related
Nov 21, 2012
I am trying to consolidate rows of data in to a single row at Cust Ref level (column A) but I need to ensure the numbers in each monthly column are added up accordingly so I have one total per month by Cust Ref. I hope what I have said makes sense. I have attached the spreadsheet so you can see what I am talking about.
View 6 Replies
View Related
Mar 18, 2014
I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
View 1 Replies
View Related
