Is a Custom Pivot Table possible?

[urnumdei]

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

 

[jim may]

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]

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]

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]

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]

Well... It's a bit outdated.... Entice! Ever heard of it?

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

 

[RoryA]

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!