| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Combo Box or Data Validation
I have a cell that includes a list of names (data validation).
I want the pivot table page field to change following the name selected. How can I do this? Is it better with a combo box? Thank you! |
|
#2
|
|||
|
|||
|
I would think it would be easiest if you made the name column a page field in
the pivottable. It'll even look kind of like data|validation. FA wrote: I have a cell that includes a list of names (data validation). I want the pivot table page field to change following the name selected. How can I do this? Is it better with a combo box? Thank you! -- Dave Peterson |
|
#3
|
|||
|
|||
|
Hello, I need to explian better.
Name is included in the page field. But it has too many names. The data validation in another page includes selected names. The purpose is to make easier for users to select the names that they need. The data is shown for the selected name in graphical form. I have all that set. I want users to select a name and be able to see the data for that name. It is a lot of data so it would be better if with the selection of the names in one page changes the name field in the pivot table. How can I do that? Is it possible? Thank you! "Dave Peterson" wrote: I would think it would be easiest if you made the name column a page field in the pivottable. It'll even look kind of like data|validation. FA wrote: I have a cell that includes a list of names (data validation). I want the pivot table page field to change following the name selected. How can I do this? Is it better with a combo box? Thank you! -- Dave Peterson |
|
#4
|
|||
|
|||
|
This seemed to work under light testing...
Right click on the worksheet tab that holds the data|validation cell. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E1")) Is Nothing Then Exit Sub Application.EnableEvents = False Worksheets("sheet2").PivotTables("Pivottable1") _ .PageFields("Name").CurrentPage = Target.Value Application.EnableEvents = True End Sub You'll have to modify the worksheet name that holds the pivottable, the name of the pivottable, the name of the page field, the address of the cell with the data|validation. I used Sheet2, Pivottable1, Name, and E1. FA wrote: Hello, I need to explian better. Name is included in the page field. But it has too many names. The data validation in another page includes selected names. The purpose is to make easier for users to select the names that they need. The data is shown for the selected name in graphical form. I have all that set. I want users to select a name and be able to see the data for that name. It is a lot of data so it would be better if with the selection of the names in one page changes the name field in the pivot table. How can I do that? Is it possible? Thank you! "Dave Peterson" wrote: I would think it would be easiest if you made the name column a page field in the pivottable. It'll even look kind of like data|validation. FA wrote: I have a cell that includes a list of names (data validation). I want the pivot table page field to change following the name selected. How can I do this? Is it better with a combo box? Thank you! -- Dave Peterson -- Dave Peterson |
|
#5
|
|||
|
|||
|
This forum is of great help!!!!!!!!!!!!!!!!!!!!!
Thank you very much. "Dave Peterson" wrote: This seemed to work under light testing... Right click on the worksheet tab that holds the data|validation cell. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E1")) Is Nothing Then Exit Sub Application.EnableEvents = False Worksheets("sheet2").PivotTables("Pivottable1") _ .PageFields("Name").CurrentPage = Target.Value Application.EnableEvents = True End Sub You'll have to modify the worksheet name that holds the pivottable, the name of the pivottable, the name of the page field, the address of the cell with the data|validation. I used Sheet2, Pivottable1, Name, and E1. FA wrote: Hello, I need to explian better. Name is included in the page field. But it has too many names. The data validation in another page includes selected names. The purpose is to make easier for users to select the names that they need. The data is shown for the selected name in graphical form. I have all that set. I want users to select a name and be able to see the data for that name. It is a lot of data so it would be better if with the selection of the names in one page changes the name field in the pivot table. How can I do that? Is it possible? Thank you! "Dave Peterson" wrote: I would think it would be easiest if you made the name column a page field in the pivottable. It'll even look kind of like data|validation. FA wrote: I have a cell that includes a list of names (data validation). I want the pivot table page field to change following the name selected. How can I do this? Is it better with a combo box? Thank you! -- Dave Peterson -- Dave Peterson |
|
#6
|
|||
|
|||
|
Just in case...
If you are using xl97 and have that data|validation list on worksheet, you may find that this doesn't work. Debra Dalgleish explains the xl97 bug at: http://contextures.com/xlDataVal08.html#Change Her work around is to place a button next to the cell with Data|Validation and then click the button after choosing the name. FA wrote: This forum is of great help!!!!!!!!!!!!!!!!!!!!! Thank you very much. "Dave Peterson" wrote: This seemed to work under light testing... Right click on the worksheet tab that holds the data|validation cell. Select view code and paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E1")) Is Nothing Then Exit Sub Application.EnableEvents = False Worksheets("sheet2").PivotTables("Pivottable1") _ .PageFields("Name").CurrentPage = Target.Value Application.EnableEvents = True End Sub You'll have to modify the worksheet name that holds the pivottable, the name of the pivottable, the name of the page field, the address of the cell with the data|validation. I used Sheet2, Pivottable1, Name, and E1. FA wrote: Hello, I need to explian better. Name is included in the page field. But it has too many names. The data validation in another page includes selected names. The purpose is to make easier for users to select the names that they need. The data is shown for the selected name in graphical form. I have all that set. I want users to select a name and be able to see the data for that name. It is a lot of data so it would be better if with the selection of the names in one page changes the name field in the pivot table. How can I do that? Is it possible? Thank you! "Dave Peterson" wrote: I would think it would be easiest if you made the name column a page field in the pivottable. It'll even look kind of like data|validation. FA wrote: I have a cell that includes a list of names (data validation). I want the pivot table page field to change following the name selected. How can I do this? Is it better with a combo box? Thank you! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| data validation lists | Excel Discussion (Misc queries) | |||
| Error Message with Data Validation | Excel Discussion (Misc queries) | |||
| data validation | Excel Worksheet Functions | |||
| Data Validation | Excel Discussion (Misc queries) | |||
| Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions | |||
Hybrid Mode
