• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

connecting pivots

On the attached worksheet you will find a sheet entitled Supplier Overview

You will see two separate pivot tables. At the moment I have to change both Suppliers separately.

Can some one tell me away of changing the supplier for both pivots from one drop down/ slicer etc?

Also can any one help me with a formula to go in cell L:10

The formula needs to count the number of customers in column A. Obviously this number will change when the chosen supplier is changed on the main pivot/slicer.
Sales-Master--YTD-.xlsx
0
robmarr700
Asked:
robmarr700
  • 4
  • 4
1 Solution
 
Rgonzo1971Commented:
Hi,

Pls try

= COUNTA(A1000)-1

Regards
0
 
robmarr700Author Commented:
That's returning a value of -1?

It should be 50
0
 
Rgonzo1971Commented:
Sorry

= COUNTA(A7:A1000)-1
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
robmarr700Author Commented:
That's great,

Are you able to help with my other query?

Rob
0
 
Rgonzo1971Commented:
In the sheet Module add this code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B4")) Is Nothing Then
    Range("F4").Value = Target.Value2(1, 2)
ElseIf Not Intersect(Target, Range("F4")) Is Nothing Then
    Range("B4").Value = Target.Value2(1, 2)
End If
Application.EnableEvents = True
End Sub

Open in new window

Regards
0
 
robmarr700Author Commented:
Sorry I'm very much a novice with coding you will have to elaborate?

Alternatively if you could place the code in for me into the attached (uptodate sheet)

And explain the steps?

Rob
Sales-Master--YTD-.xlsx
0
 
Rgonzo1971Commented:
Hi,

On the Supplier Overview sheet Tab right click and choose show code

Regards
Sales-Master--YTD-V1.xlsm
0
 
robmarr700Author Commented:
Spot on, works perfectly.

Rob
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now