Solved

# connecting pivots

Posted on 2014-08-19
53 Views
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
Question by:robmarr700

LVL 47

Expert Comment

Hi,

Pls try

= COUNTA(A1000)-1

Regards
0

Author Comment

That's returning a value of -1?

It should be 50
0

LVL 47

Expert Comment

Sorry

= COUNTA(A7:A1000)-1
0

Author Comment

That's great,

Are you able to help with my other query?

Rob
0

LVL 47

Expert Comment

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
``````
Regards
0

Author Comment

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

LVL 47

Accepted Solution

Hi,

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

Regards
Sales-Master--YTD-V1.xlsm
0

Author Comment

Spot on, works perfectly.

Rob
0

## Featured Post

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.