Combo Box or Data Validation

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

This forum is of great help!!!!!!!!!!!!!!!!!!!!!
Thank you very much.

 

[Dave Peterson]

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.

This forum is of great help!!!!!!!!!!!!!!!!!!!!!
Thank you very much.

 

[Guest]

You see, that is the problem, after one success one becomes greedy:
Now I want the data validation field to adjust to the different lenghts that
the list of names has.

Any suggestions?

 

[Guest]

One more thing: The list of names excluding Grand Total...

 

[Dave Peterson]

So some names are 8 characters and some are 30. And you can't see all 30
characters when the dropdown opens.

About the best you can do is to widen that column.

Another option is to merge a couple of cells and use that merge area for
data|validation. But merged cells can make life difficult--sorting, filtering,
....

You see, that is the problem, after one success one becomes greedy:
Now I want the data validation field to adjust to the different lenghts that
the list of names has.

Any suggestions?

 

[Dave Peterson]

Ps.

Select a couple of cells (like A1:B1) and then
format|cells|alignment tab|check merge cells

You see, that is the problem, after one success one becomes greedy:
Now I want the data validation field to adjust to the different lenghts that
the list of names has.

Any suggestions?

 

[Dave Peterson]

Remove Grand Total from the list of names. That's not a name that occurs in
your data.

One more thing: The list of names excluding Grand Total...

 

[Guest]

That was quick. But I think my question was not worded right. Plesae excuse
me as english is not my first language.
Here I go:
I want to include another data validation field that controls another pivot
table filed, this time in the same spreadsheet.
This drop down will change the list of names. Sometimes the list is longer
sometimes is shorter. I would like it to be automatically adjusted to the
list of names excluding the grand total name.
The other thing is that I would like to hide the other sheet. Right now I
can't do it and run the change.
Your help is great... maybe you can suggest some reading...

 

[Dave Peterson]

Maybe you can use a dynamic range name that grows and contracts with the list.
Debra Dalgleish explains it:
http://contextures.com/xlNames01.html#Dynamic

I'm not sure why you can't hide the other sheet.

There's lots of links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

That was quick. But I think my question was not worded right. Plesae excuse
me as english is not my first language.
Here I go:
I want to include another data validation field that controls another pivot
table filed, this time in the same spreadsheet.
This drop down will change the list of names. Sometimes the list is longer
sometimes is shorter. I would like it to be automatically adjusted to the
list of names excluding the grand total name.
The other thing is that I would like to hide the other sheet. Right now I
can't do it and run the change.
Your help is great... maybe you can suggest some reading...

 

[Guest]

The offset formula worked for the size of the list.

I can't hide the sheet because it select the sheet in the command for the
data validation.

How can I include two data validation fields that make a pivot table action?
I copied the code but it didn't work.

Thanks again,

FA

 

[Dave Peterson]

I don't understand what you mean about selecting the sheet for the
data|validation stuff.

I would have guessed that you set up data|validation manually (using a range
name on that other sheet). Then that other sheet doesn't have to be selected.

And maybe if you have two cells and two page fields:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("E1,F1")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Select Case LCase(Target.Address(0, 0))
Case Is = "e1"
Worksheets("sheet2").PivotTables("Pivottable1") _
.PageFields("Name1").CurrentPage = Target.Value
Case Is = "f1"
Worksheets("sheet2").PivotTables("Pivottable1") _
.PageFields("Name2").CurrentPage = Target.Value
End Select

Application.EnableEvents = True

End Sub

(untested, but it did compile.)

The offset formula worked for the size of the list.

I can't hide the sheet because it select the sheet in the command for the
data validation.

How can I include two data validation fields that make a pivot table action?
I copied the code but it didn't work.

Thanks again,

FA

 

[Guest]

You are right I can hide the sheet. It works now.

But the code to use two data validation fields (independent from each other)
do not work.

You have been of great help.

How can I do this?

 

[Dave Peterson]

It worked for me in my simple test.

Are you sure you used the correct pivottable name?

Are you sure you used the correct pagefield name?

(difficult to guess what went wrong for you.)

You are right I can hide the sheet. It works now.

But the code to use two data validation fields (independent from each other)
do not work.

You have been of great help.

How can I do this?

 

[Guest]

I presented my work and obtained great feedback. For that I thank you.
Good Luck!