Is a Custom Pivot Table possible?

urnumdei

New Member
Joined
Jul 10, 2008
Messages
30
I want to make a pivot table like table. It pulls information, sorts dates in chronological order, and counts how many of those dates there are.

Since I work in Telecommunications, lets say that we have a callers who made 5 calls on 7/11, 2 on 7/12, and 6 on 5/30.

So my source data would have this kind of look:

Column A Column B
John Doe 7/11/08
Jane Cook 5/30/08
Billy Joe 7/12/08

And so forth. There isn't a pattern in the list of Callers or dates. There could be a series of Johns, then a Jane, back to Johns again. And the same with dates. It could be 7/11 for a bit, then 5/30 once, then back to 7/11. (Our CRM is really dumb, and information isn't always the most organized.)

SO the table I want to make looks like this:

Column A Column B
5/30/08 6
7/11/08 5
7/12/08 2

A pivot table can do what I just explained, but here's the catch. I need it to be auto refreshable. I change the source data, and the table auto updates. Is this possible.

Also, let's go a bit further....

How can I make a table that gives a summary of how many calls were made within the week, 2 weeks ago, 3 weeks ago , and how many calls made over 4 weeks ago. I'm sure it's just a =SUM() formula, but I think that it won't work because the "pivot table" will change that will return the SUM into a #REF!.

Thanks
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
In the Layout of your PT, use Column B (The Dates called in) as BOTH
the Row and as Data; Use the Count function for the Data piece.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can right-click the date field and Group it by week for exampe if you want sumaries at that level. You can also use Worksheet _Change event code to refresh the table but if your data is coming from elsewhere it might make more sense to have a button to refresh the table (or simply refresh whenever the sheet contining the pivot table is activated.) Additionally, it may be possible, if you have an ODBC link to your CRM data, to link the pivot table directly to your CRM data.
 

urnumdei

New Member
Joined
Jul 10, 2008
Messages
30
How would I know if my CRM is ODBC/Excel friendly? I've heard of such wonders, but I haven't been able to dabble in it yet. Right now, our "link" is a person exporting information out of CRM and then dumped into the spreadsheet. (A process that takes too long in my opinion.

If I could ODBC it, then that would get rid of tabs it seems.... That would be amazing, and give me opportunity to be lazy.... er, do other "work" ;)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

If it's an off-the-shelf CRM product, I would expect it to have an ODBC driver. You might check with the supplier.
 

urnumdei

New Member
Joined
Jul 10, 2008
Messages
30
Well... It's a bit outdated.... Entice! Ever heard of it?

We also use Oracle. I'm sure that's at least compatible
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Oracle you can definitely do as long as you have the Oracle client installed on your machine. Entice I have heard of, but know nothing about!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,143
Members
415,880
Latest member
Bruce0203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top