Hyperlinking/Data Validation?

[sstilwell]

Hello All,

I have a hyper-linking or possibly a Data Validation question.

I have Sheet 1, with a bunch of customer on it(Each customer has their own row). It also has some financial data on it as well, but not important.

I also have Sheet 3, which is a Pivot Chart of all the customers on Sheet 1. It has a chart for each customer from Sheet 1 and some additional financial data. The "Reporter" field on the Pivot Chart is the Customers. So select which customer data/chart you want to view from that drop down.

I want to link each customer from Sheet 1 to their respective Pivot Chart on Sheet 3. So when you click on them it will automatically take you to the correct pivot chart for that customer from the Pivot Chart drop down menu for customers.....

Is this possible??

I have though about using a Data Validation of "List" from the Pivot Chart but am not sure how I can set that up to grab a specific customer and auto populate it's financial data and chart from the Pivot Chart.

Thanks All. Quiet Important to our current Dashboard I am setting up. I don't want to have to use hyperlinks to individual sheets on the same workbook cause we have 50 + customer within this segment and it would be to many sheets.

 

[Smitty]

Welcome to the Board!

I just did something similar, updated a series of non-related Pivot Tables using a single DV selection, which fires a Change event:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> pt <SPAN style="color:#00007F">As</SPAN> PivotTable<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("X3")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br>            <SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> 7<br>                <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pt <SPAN style="color:#00007F">In</SPAN> Sheets(i).PivotTables<br>                    <SPAN style="color:#00007F">With</SPAN> pt.PivotFields("Physician ID")<br>                        .ClearAllFilters<br>                        .CurrentPage = rng.Value<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> pt<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>            </FONT>

If you're using Excel 2010, you could also think about using Slicers.

HTH,

 

[sstilwell]

Thanks Smitty. I am using Excel '10.

I tried the Splicers, but I can't find a way to "hide" them once created and hyperlink them to Sheet 3, from Sheet 1.

Can you please explain your DV change event in more details....step by step maybe? DV for dummies? We don't have an SQL server to grab info from either...

What is the DV validation Criteria that was used?

Thanks.

 

[Smitty]

The Data Validation corresponds with the list in each PT's filter range.

The way the change event works is that you tell it to monitor the cell where the DV is setup, then when a DV selection is made the code fires. In the case of my example the code loops through the sheets with PT's on them and sets the filter value to the DV selection, thereby updating each PT to the selection made.

You can also use the DV to select individual worksheets like this:

Sheets(Target.Value).Activate

Provided the names in the DV list correspond to your worksheet names.

 

[sstilwell]

Mmmm. I am still lost. I know how to create the DV for list validation based on the customers from Sheet 1.

How do you set up the change event to search for the DV for a customer and return the PV for it. Where do you create the change event*

Sorry for being a noob.

 

[Smitty]

You can record a macro updating your pivot tables. That will capture the respective PT names and Filter fields.

As for the Change event code, you can copy the guts of what I posted.

This is where you set the Validation range:

Set rng = Target.Parent.Range("X3")

The code goes in the worksheet specific module. You can get there by right-clicking on the sheet tab, then select View Code.

 

[sstilwell]

I had trouble importing the Macro's into the spreadsheet. I enabled the spreadsheet to hold macro's. Clicked Record Macro's and the one below. I steped into it/ran it and it stops out and the line stated below. My workbook is called Home Dashboard. The sheets name that I want to apply the macro to is "Sheet3"
Macro:
Sub Customer1() -"Does this need the be the name of the spreadsheet?"

Dim rng As Range
Dim pt As PivotTable
Dim i As Long

'Set Target Range, i.e Range("A1, B2, C3), or Range("A1:B3")
Set rng = Target.Parent.Range("A1") --Macro's stops out at the line. What's wrong here?
'Only looks at the signle cell change/reference of the PV
If Intersect(Target, rng) Is Nothing Then Exit Sub
'Action is conditional

For i = 8 To 8 --does these represent the sheet # that the macros needs to be applied too?
For Each pt In Sheets(i).PivotTables
With pt.PivotFields("Tenant Name")
.ClearAllFilters
.CurrentPage = rng.Value
End With
Next pt
Next i

End Sub
-----

Thanks a bunch for your help Smitty

 

[Smitty]

The procedure needs to be named:

Private Sub Worksheet_Change(ByVal Target as Range)

And it needs to go in the worksheet specific module. Since it's an event you can't run it; it will only run in response to a change on the worksheet. But you can put a Breakpoint (F9) in the code and step through it once it's fired.

Your code stopped because there was no reference to the Target range, as there is with a change event.

8 to x would represent the sheets that the code needs to work on. In my case it was sheets 3-7.