Solved: Big Automation Question with Pivot Tables and Graphs

[Eric58132]

Hi folks,

Ok my terminology may be wrong here, so I'm going to try and be as specific as I can.

I have a large pivot table, and a graph that changes based upon what is chosen within the pivot. What i'm trying to do is find a way to make the graph titles change as well. In order to do this, I'd like to obtain the list of chosen filters within one pivot field. Is there a way, either through vba or some formula, to place this list somewhere on my sheet outside of the pivot table? Then I can add the chosen filter names to my concatenate formula. If you think you may be able to help me but this question isn't clear, please don't hesitate to ask me to re-word whatever doesn't make sense. I will be here at my desk for at least the next hour, and all day on Monday.

Thank you!

Edit: I have attached a picture for help. Based upon this, I'd like Beverly, Essex, Lowell and Malden to be listed somewhere outside of my pivot table.

[Aussiebear]

Can you post a sample workbook?

[Eric58132]

Here is an example of what I'm trying to do. I know I could just automatically create a pivot chart, but my "user audience" also wants to breakout chart on the left (and it's preferable to have everything on one sheet tab.) In addition, the chart bars end up being an overall reflection of multiple questions (where in my vbaexpress example each question is represented individually.) What i'm trying to do is obtain the dynamic list of chosen options within the town and company dropdowns in the pivot table, which will be the criteria for naming my graph.

Does this help?

[mdmackillop]

Put this in a standard module
[VBA]
Function Towns(data As Range)
Dim cel As Range, t As String
For Each cel In data
If cel <> "" Then t = t & cel & ", "
Next
Towns = Left(t, Len(t) - 1)
End Function

[/VBA]

and in say Z4
=towns(Z16:Z100)

Is that what you are after?

[Eric58132]

This is great Md, and 99% of what I was looking for. While running the module, it listed all of the Towns in one cell. Is it possible to list the second town in the cell below the first one, and so on?

Either way, thanks for your help.

[mdmackillop]

Run this to list towns in the activecell and below

[vba]Sub Towns()
Dim cel As Range, t As String
For Each cel In Range("Z16:Z100")
If cel <> "" Then
ActiveCell.Offset(i) = cel
i = i + 1
End If
Next
End Sub[/vba]

[Eric58132]

this is perfect, thanks a ton.

[Eric58132]

Actually, one last question..... after reading your update, I assumed it worked. I'm sure it still does, but I just don't know where to put it. Does this replace the original code, or is it in addition to it? If I just add it below the original module code, and then call it (in Z4 as you suggested), I get an error saying that the name isn't valid.

[mdmackillop]

This replaces the original code. The first works as a Function and returns a value. The second writes values into the cells.

[Eric58132]

Ok i'm sure I'm overlooking something very easy here. Is that code placed in a module, just as the original? Or is it placed in the sheet code? Then, assuming I want it to run beginning in cell Z4 (as the original example suggested) do I just type =Towns(Z16:100) ? Right now the code is in module1, and I was typing =Towns(Z16:Z100) in cell Z4.... I'm getting a "That name is not valid" error.

Apologies for the inexperience. And thank you for helping.




Edit: Ah, I see. It needs to be "run(Towns)" in the Intermediate window of VBA. I'm looking for this portion of my file to update automatically upon pivot table change, so I think I'll have to use your first version of the code and find a way to make it work. Thanks for the help MD, I appreciate it.

[mdmackillop]

Try this version