Excel 2010 :: Unhiding Specific Number Of Worksheets Based On Cell Value
Jun 26, 2013
I have been attempting to cobble together code from different forums for the past several hours to make a macro to unhide a specific number of worksheets based on a cell value, and have failed miserably thus far. Here are the specifics of what I have going on:
Microsoft Excel 2010
Workbook Name: TASERR Model - 06-26-13.xlsm
In this workbook, I have a worksheet called "Input". In cell C21 of that worksheet, workbook users will fill in a number between 1 and 50 (I have the value for that cell limited to those numbers through data validation). Based on the value in that cell (C21), I want to make a macro to unhide that specific number of hidden worksheets. I have 50 hidden worksheets which are named "Route (1)", "Route (2)", "Route (3)", etc.. If the value in cell C21 is 7, I want the worksheets named "Route (1)", "Route (2)", "Route (3)", "Route (4)", "Route (5)", "Route (6)", and "Route (7)" to unhide. If the value in cell C21 is 3, I want the worksheets named "Route (1)", "Route (2)", and "Route (3)" to unhide. I'm sure y'all can see the functionality I'm going for at this point.
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
I have 2 lists (excel 2010) that I need to compare (they are currently 2 files, but I can combine them into 1 file with 2 worksheets) if it would be better.
The first list is of "All Students" at our college. The second list is those students who live "ON campus". I need a list of those students who live OFF campus.
The common denominator headers in both lists is: A1 Last Name A2 First Name
I would like to keep the "All Students" list as my master as it contains all the data I need such as addresses.
Ideally, I would like to create a macro or lookup or whatever that will take everyone from the "ON" list and remove them from the "ALL" list, leaving me with the data I need.
how to run some VBA code (written by someone else, unfortunately) only when clicking once in cell A1, and not run in any other cell. This is being run in Excel 2010. The code I would like to run in this manner is below, and currently will run when the user clicks on any cell in the worksheet it is applied.
[ Option Explicit_________________________________________ Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'If IsDate(Target.Cells(1, 1).Value) Then Set DatePickerForm.Target = Target.Cells(1, 1) DatePickerForm.Show vbModal Cancel = True 'End If End Sub ]
What I'm trying to do is, create a formula that works like vlookup, except I want to return the value of the cell that is a specified number of cells below the looked-up data in a single column array. So, let's say cell A1 reads "Bacon". In B1, I'd like to put a formula which searches the array for the cell that contains "Bacon" and then returns the value of the cell underneath, let's say the value is "Eggs". Then in C1, I'd like to put another formula which also looks up "Bacon" in the array, then returns the value of the cell that is down two cells from the cell that contains "Bacon", let's say the value is "Milk". And then in D1, a formula that returns the value 3 cells below "Bacon", and so on. All the values here will be text, and not sorted in any specific order. I'll be using Excel 2010.
Please take a look at the attached sheet if it clarifies things.
I'm using Excel 2010 and I'm having troubles displaying hiding my column B if none of the cells of column A contains either "(Quasi Echec)" or "(Quasi Russite)" at least once.
PHP Code: [URL] ......
Below are my 3 attempts.
Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim c As Range If Target.Column 1 Then Exit Sub Application.EnableEvents = False On Error Resume Next
[Code] ..........
Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sub GetUniquesInColA() Dim rng As Range Dim c If Target.Column 1 Then Exit Sub On Error Resume Next
[Code] .........
Code: Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim x As Long Dim LastRow As Long LastRow = Range("A65536").End(xlUp).Row
I am trying to work out a macro to hide and unhide rows in worksheet 6 based on a cell input (cell I6) in worksheet 1 based on the following:
>> If I6 in Worksheet 1 = 1 or 5 or 6 then Hide Rows 19 to 24 in Worksheet 6 >> If I6 in Worksheet 1 = 2 then Hide Rows 21 to 24 in Worksheet 6 >> If I6 in Worksheet 1 = 3 or 4 or 7 then Hide Rows 17 to 20 in Worksheet 6 >> If I6 in Worksheet 1 = 8 or 9 then Hide Rows 17 to 20 and Rows 23 to 24 in Worksheet 6
My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.
If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows
I would need two macros for one of my projects. I did search the forum, but I couldn't find anything what would suit my needs.
The first macro should hide those rows which would contain 0 (zero) in a specific column (in my workbook it's E). The secon macro should unhide the hidden rows.
I have a large spreadsheet which includes many rows whose number format needs to change depending on the value of a specific cell.
So for example:
If cell B20 has value "A", cells Q20:AG20 should display as 1,234 If cell B20 has value "B", cells Q20:AG20 should display as 5.6% If cell B20 has value "C", cells Q20:AG20 should display as 7.89 Etc...
As conditional formatting does not seem to include a number format option I've worked out that I need some sort of macro to change these settings for me, either automatically whenever the value of B20 changes or, more likely, when I manually choose to run the macro.
I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.
I have a 2010 Excel spreadsheet where I have a macro set up to print the page. What I need it to do though is to increase a number in a cell and display it each time I print the page.
So I have this list (I made it a little bit shorter).
So what you see is two different tasks (01 and 02) and three different conditions (A, B and C). In column B you see the result I would like to have. '/Searchtask_01.html' in A1 belongs to conditions A, because it is in session A. However, '/Searchtask_01.html' in A10 belongs to conditions B, because it is in session B.
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001 q9002 q9003 q9004 q9005 q9006 etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due. Column C is when bi-weekly inspection is due. Column D is when monthly inspection is due. Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1 B1 C1 D1 E1
Inspection Date Weeekly Due Bi-WeeklyDue Monthly Due 6 Monthly Due
January 1 2013 Change red Jan 8 & return blank Jan 10 Change red Jan 15 & return blank Jan 17 Change red Feb 1 & return blank Feb 3 Change red June 1 & return blank June 3
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:
Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("A1").Value Case Is < 2.5 Sheet1.Tab.Color = vbRed Case Is > 2. 5, Is < 4 Sheet1.Tab.Color = vbGreen End Select End Sub
The cell I'm using is F2 and my cell value is simply y or n. I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.
I have a massive spreadsheet (ca.110 000 rows [excel 2010). I need to cut this monster by deleting all the cells that do not have a certain cell value within them.
Say that in my column A there are different entries (text type). I would like to delete the ones that do not match the following criteria:
"mytextascriteria*", where * somehow stands for the rest of the string.
Strictly speaking I can only determine beggining of the string I want to perserve but the values at the end of that string are changing.
I found the thread about deleting entire row based on a cell value here: Delete entire row based on one cell?
but I couldn't quite work out how to apply it in my scenario....
Otherwise is completely pointless to do it manualy with these amout of records.
I'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
Is there a way in Excel (2010) to lock data from being entered into cells based on the value in another cell? Here is an example:
Row 4 contains dates from 2015-2030 as a header starting at column C (C4 and on) that represents years of revenue. Column B contains dates as well, this date can be any year but this becomes the driver for the data input in columns C through X. Column B contains the "Delivery Date"
Data is input in Columns C and so forth. The issue becomes that let's say that the date in C4 is 2016 and this is the beginning year. Obviously there should be no inputs for 2015 (C5) and the data should start to be input for this year at 2016 (C6). Often people just begin filling in the first available year not looking at what the year actually is.
Is there a way to add a formula or some logic/protection to prevent inputs in previous rows based on the values in column B? So if the value in column B is XXXX than there can be no inputs in cells less than that value?
