| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditionally hiding rows
can anyone help me figure out this problem?
I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Functions and formulas return values. They do not hide rows or such. The
closest you get is conditional formatting which changes the format of a cell but it can not hide rows. Filtering hides rows but it is not automatic as I suspect that you want. The only thing left is macros which can work if you want to go there... -- HTH... Jim Thomlinson "Derrick" wrote: can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Ok. Thanks.
As far as filtering/macros, i'm willing to go there - but i have a very limited understanding of what is involved. So, if you're willing to go step by step, i'm willing to learn. otherwise, i'll have to see if i can create a new design for the spreadsheet layout. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
You would want a macro tied to a drop down or a change_event with a select
case imbedded. -- Don Guillett Microsoft MVP Excel SalesAid Software "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Haha, Thanks!
except, I've never used macros before, so im virually clueless to what the code is. can you help? "Don Guillett" wrote: You would want a macro tied to a drop down or a change_event with a select case imbedded. -- Don Guillett Microsoft MVP Excel SalesAid Software "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
I will assume the dropdown box relates to cell A1 on Sheet1
Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Right now, you surpass Batman on my list of awesome superheros.
Thank you! "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Great feedback! I'm off to buy a large-sized hat
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... Right now, you surpass Batman on my list of awesome superheros. Thank you! "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Don't forget the cape
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bernard Liengme" wrote in message ... Great feedback! I'm off to buy a large-sized hat -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... Right now, you surpass Batman on my list of awesome superheros. Thank you! "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
I'll throw in the utility belt if you can show me how to modify the code to
target a separate worksheet/cell within my workbook...worksheet values are name="Change Form" range=E5 :-) "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Wanna try explaining this again. -- Don Guillett Microsoft MVP Excel SalesAid Software "Michele" wrote in message ... I'll throw in the utility belt if you can show me how to modify the code to target a separate worksheet/cell within my workbook...worksheet values are name="Change Form" range=E5 :-) "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
Let me try...
I'm active on Sheet2, and want to hide rows in Sheet2 based on the dropdown selection on Sheet1 cell K1. I added the code below to Sheet1 with the dropdown in Sheet 1 cell K1 and when I used the dropdown it worked slick. I then added the code to Sheet2 (modifying the range to b1), but then I used a formula in Sheet 2 cell b1 to pull the value of the dropdown on Sheet1 cell k1 I got nothing. I'm guessing it's not reading the off sheet reference? Thanks~ Michele "Don Guillett" wrote: Wanna try explaining this again. -- Don Guillett Microsoft MVP Excel SalesAid Software "Michele" wrote in message ... I'll throw in the utility belt if you can show me how to modify the code to target a separate worksheet/cell within my workbook...worksheet values are name="Change Form" range=E5 :-) "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditionally hiding rows
sheet EVENT code must be in the sheet it is trying to change If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Michele" wrote in message ... Let me try... I'm active on Sheet2, and want to hide rows in Sheet2 based on the dropdown selection on Sheet1 cell K1. I added the code below to Sheet1 with the dropdown in Sheet 1 cell K1 and when I used the dropdown it worked slick. I then added the code to Sheet2 (modifying the range to b1), but then I used a formula in Sheet 2 cell b1 to pull the value of the dropdown on Sheet1 cell k1 I got nothing. I'm guessing it's not reading the off sheet reference? Thanks~ Michele "Don Guillett" wrote: Wanna try explaining this again. -- Don Guillett Microsoft MVP Excel SalesAid Software "Michele" wrote in message ... I'll throw in the utility belt if you can show me how to modify the code to target a separate worksheet/cell within my workbook...worksheet values are name="Change Form" range=E5 :-) "Bernard Liengme" wrote: I will assume the dropdown box relates to cell A1 on Sheet1 Right click the Sheet1 tab; use View code and paste this subroutine Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Rows("22:29").EntireRow.Hidden = False If Range("A1") = "A" Then Rows("23:24").EntireRow.Hidden = True Rows("26:26").EntireRow.Hidden = True ElseIf Range("A1") = "B" Then Rows("25:25").EntireRow.Hidden = True Rows("27:28").EntireRow.Hidden = True ElseIf Range("A1") = "C" Then Rows("21:22").EntireRow.Hidden = True Rows("29:29").EntireRow.Hidden = True End If End Sub Whenever the value inA1 changes, the subroutine will run Just alter "A1" to suit your needs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Derrick" wrote in message ... can anyone help me figure out this problem? I have an drop down option list, and i would like to create an if statement that will hide select rows depending on which option is selected: ie if option A is checked, then rows 23,24, 26 are hidden. if option b is checked, rows 25, 27,28 are hidden and if option c is checked rows 21 22 29 are hidden. simple enough i hope? thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditionally Hiding or Showing the cell content | Excel Discussion (Misc queries) | |||
| Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
| Conditionally formatting rows | Excel Worksheet Functions | |||
| Conditionally Hiding Rows | Excel Discussion (Misc queries) | |||
| Hiding Rows Conditionally | Excel Discussion (Misc queries) | |||
Linear Mode
