Solved: Pivot Chart Help

[jo15765]

I have a pivot that I use so the data will refresh once the workbook is opened. This pivot down the left contains sales-reps names (over 1500) and across the top contains a list of items the company sells (over 3000). All of this is on sheet1, sheet2 contains the pre-formatted (which of course never matches up with sheet1) sheet of how the data needs to be presented. I have been just manually matching up the data using cell references, but this has grown to be TOO large of a workbook for me to do this when either a new product is added or a new employee is added. Is there a way for me, through VBA or a different cell reference to match up worksheet2 with the figures on worksheet1?

I am attaching a very very small snippet just so you can get a feel of what I am talking about and hopefully provide some options.

Thanks in advance for helping guys, it is SOOO appreciated.

[mdmackillop]

Your Products contain some typos, so you will see Value errors


Cell B2 =PIVOT($A2,B$1)

[VBA]Function Pivot(Sales, Product)
Dim Sh As Worksheet
Dim S, P
Set Sh = Sheets(1)
S = Sh.Columns(1).Find(Sales).Row
P = Sh.Rows(1).Find(Product).Column
Pivot = Sh.Cells(S, P)
End Function[/VBA]

[jo15765]

I can't get this to run?! I am probably doing something silly. If I have saved my workbook as a macro enabled workbook and added the code as a new module. I am then pressing F5 to run the code, but it pops up a prompt asking me what macro to run, and the macro I added in (your code) does not show in the list?

[Aflatoon]

Cell B2 =PIVOT($A2,B$1)

It's a UDF - that quote shows how to use it.

[jo15765]

It's a UDF - that quote shows how to use it.

Ohhhh, thank you for pointing that out, I thought that was pointing out one of the errors.

Duh!

[jo15765]

Your Products contain some typos, so you will see Value errors


Cell B2 =PIVOT($A2,B$1)

[VBA]Function Pivot(Sales, Product)
Dim Sh As Worksheet
Dim S, P
Set Sh = Sheets(1)
S = Sh.Columns(1).Find(Sales).Row
P = Sh.Rows(1).Find(Product).Column
Pivot = Sh.Cells(S, P)
End Function[/VBA]

That works amazingly!

One follow-up question that I do have:
And any time there is a change to the sales person name or the item list, I would just need to add the code to Cell B2 and drage it over/down and all items will match up again?

[mdmackillop]

And any time there is a change to the sales person name or the item list, I would just need to add the code to Cell B2 and drage it over/down and all items will match up again?

No, the formula will update automatically.

[Aflatoon]

If you actually have a pivot table (unlike your sample!) then GETPIVOTDATA may well do what you need

[jo15765]

If you actually have a pivot table (unlike your sample!) then GETPIVOTDATA may well do what you need

I do have a pivottable on sheet1...I just copied/pasted a snippet of data only onto the worksheet.

How does GETPIVOTDATA work?

[Aflatoon]

Easier to show than explain! See attached workbook where I added a pivot from your sample data.

[jo15765]

This will actually work very nicely for another piece I need to set-up. How would I set-it up to remove the salesperson's name, but show a Total Count of Each Item sold?

(It's actually different than that but the principle of what needs to be shown is the same)

[Aflatoon]

I'm not following-sounds like a simple SUM of the data?

If not, you can just type = and select a cell in the pivot to get the GETPIVOTDATA formula you need.

[jo15765]

Yes and no. I knew I could just set the cell reference that way, but am trying to take into account expansion or if the pivot data happens to alter cells.

[Aflatoon]

You misunderstand. Selecting a cell in the pivot should generate a GETPIVOTDATA formula for you (unless you turned that facility off) and that formula will always return the required data no matter where that should be in the table. The only fixed reference a GETPIVOTDATA formula requires is the first cell in the pivot which should never move.

[jo15765]

You misunderstand. Selecting a cell in the pivot should generate a GETPIVOTDATA formula for you (unless you turned that facility off) and that formula will always return the required data no matter where that should be in the table. The only fixed reference a GETPIVOTDATA formula requires is the first cell in the pivot which should never move.

You are correct. I didn't realize that GETPIVOTDATA would auto-update the cell references.

Thank you for providing that insight.