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.

View 8 Replies


ADVERTISEMENT

Searching And Unhiding Specific Worksheets Using VB?

Mar 12, 2014

I have five worksheets hidden in my workbook. All of these worksheets have either "Yes" or "No" written in cell C4. (3-yes, 2-no)

I also have a search bar in the form of a textbox and a command button.

I want to be able to type "No" in the textbox, click the command button and have the pages with "No" in cell C4 to unhide.

Some screenshots if necessary:
Sheet1 (yes): Gyazo - ce85d71bc03cd2ceaa1030e22445800f.png
Sheet2 (no): Gyazo - 65f876d6e7d749bd1ca25ad957fbda99.png
Sheet3 (yes): Gyazo - 0cb442f1d34e7d744884f73e1afe2646.png
Sheet4 (yes): Gyazo - 6f0ce150322208fd93dc5fc6f4f66481.png
Sheet5 (no): Gyazo - 772f48f8a82e53713794e6655fe56fca.png
master: Gyazo - 1b71470f3de47cef347050588bca819c.png

View 4 Replies View Related

Hiding And Unhiding Rows In Multiple Sheets Based On Specific Cell Value With VBA

Jan 4, 2013

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.

View 9 Replies View Related

Excel 2010 :: Compare 2 Worksheets - Eliminate Those Falling Under Specific Criteria

Jul 2, 2012

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.

View 4 Replies View Related

Excel 2010 :: Sum Based On Criteria In Specific Column?

Oct 20, 2011

how can i sum base on the Criteria in Columns G, in this sample the sum should be 67.

Sheet1ABCDEFG1V. GoodGoodFairBadN/AReported29853N/A8Bad31085328N/A410953N/A8Good510853N/A8Good61085378Good77853N/A8V. Good810853N/A8Good910753N/A8Reported108853N/A8Good11585308N/A1210653N/A8Good1367Excel 2010Worksheet FormulasCellFormulaG13=67

View 9 Replies View Related

Excel 2010 :: VBA - Run Code Only When Clicking In Specific Cell?

Dec 23, 2013

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
]

View 3 Replies View Related

Excel 2010 :: How To Return Value Of Cell Beneath A Cell With Specific Value

Jun 14, 2014

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.

View 3 Replies View Related

Excel 2010 :: Copy Entire Row If Cell Contains Specific String

Apr 5, 2012

I am a total newb to excel and vb, and only have minimal experience in embedded C.

I have a very large spreadsheet with 9 columns and the max number of rows that excel supports (office 2010).

What I want to do is copy the entire row of data IF a cell in that row contains a specific string I'm interested in.

If it's necessary, the column that contains the values that would trigger a row copy would be column C and the data would be pasted onto a new sheet.

View 1 Replies View Related

Excel 2010 :: Display Column If Single Cell Has Specific Value

Nov 27, 2012

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

[Code] ...........

View 2 Replies View Related

Hiding / Unhiding Rows Based On Cell Input

Jul 2, 2009

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

View 9 Replies View Related

Excel 2010 :: Sort Worksheets Alphabetically And Keep The Data In Worksheets

May 15, 2013

I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.

View 2 Replies View Related

Hiding And Unhiding Two Sets Of Worksheets

Oct 11, 2008

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

View 11 Replies View Related

Hiding/unhiding Specific Rows Containing Zero

Jul 23, 2008

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.

Sound so easy ... but sadly I can't do it.

View 14 Replies View Related

Conditional Number Format Based On The Value Of A Specific Cell

May 9, 2009

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.

View 9 Replies View Related

Excel 2010 :: How To Separate Text From A Phone Number In One Cell

Jan 11, 2014

I have a 2010 version of MS Excel. I have roughly 10000 cells that I need to separate into two columns from one cell.

Here is an example of one cell "John Smith 888-8888".

View 14 Replies View Related

Excel 2010 :: Dividing Numbers By Number Of Cell They Are Found In?

Feb 13, 2012

I am using excel 2007 or 2010.

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.

View 3 Replies View Related

Excel 2010 :: Printing Spreadsheets With Increasing Number In A Cell

Feb 5, 2014

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.

View 9 Replies View Related

Excel 2010 :: Extracting Highest Number From A Cell Containing Alphanumeric

May 10, 2014

I have a cell containing alpha and numeric. I would like to extract the highest number in the cell

the cell looks like this

the answer I would like is

====G99876554321000
9

T1111111100000000
1

I am using Excel 2010

View 3 Replies View Related

Assign Specific Number To Cells Based On Another Cell Value In String

Feb 27, 2013

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.

How to get the results in B with a formula?

View 1 Replies View Related

Excel 2010 :: Copy Data From One Cell Based On Color Of Another Cell To Different Worksheet

Jan 30, 2014

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)

View 2 Replies View Related

Excel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value

Jul 2, 2012

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.

(Using Excel 2010)

View 7 Replies View Related

Excel 2010 :: Repeat Cell Data Based On Another Cell Column Dragging Down

Apr 24, 2014

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.

Excel 2010
A
B
C
1
Helper Column
Desired Result

[Code].....

View 6 Replies View Related

Excel 2010 :: Change Colour Of Cell Based On Date In Other Cell?

Jan 10, 2013

I am trying to create a sheet in XL 2010.

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

Ive also attached the worksheet

View 1 Replies View Related

Excel 2010 :: Display Cell Ranges From Multiple Locations Based On 2 Cell Values?

Jun 4, 2013

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.

P.S. I have excel 2010 and windows 7

View 1 Replies View Related

Excel 2010 :: Formula Based On YES / No Input In Another Cell

Apr 30, 2014

Two formulas which I need in Cells E74 and E75.

Basically the formulas will depend whether the entry in D73 are Yes or No

in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70

in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70

Excel version 2010

View 4 Replies View Related

Excel 2010 :: Changing Tab Color Based On Cell Value?

Mar 6, 2014

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.

View 10 Replies View Related

Excel 2010 :: How To Delete Rows Based On Cell Value

Sep 23, 2011

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.

View 5 Replies View Related

Excel 2010 :: Deleting Rows Based On Cell Value

May 21, 2012

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.

Code:
Range("AC1").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-27])+2"
Calculate
Range("AC1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & Range("AC1").Value:"AA1048576").Select
Selection.Delete

View 5 Replies View Related

Excel 2010 :: Count Formula Based On Another Cell

Aug 7, 2012

The formula I found is close but I need it to count, not sum.

=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)

Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]

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.

View 3 Replies View Related

Excel 2010 :: Enable Cell Locking Based On Value In Another Cell?

Jul 17, 2014

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?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved