I am working on a worksheet where I need to be able to draw data from a pivot table in one sheet into another sheet. If someone can assist me I would greatly appreciate it. Hopefully the following helps you understand how my workbook is organized.
Sheet "Clients" lists the client account number, name, and other data.
Sheet "Products" lists each product owned by each client and includes account number, the type of product, and the value of the product.
All clients own multiple products of varying quantities, and generally they also own several products of each type, with each product owned listed in its own row such as this:
<TBODY>
</TBODY>
In the "Clients" sheet I would like to be able to enter the account number in a cell and then in an array of other cells have the sum of each product type pulled out of the pivot table from the other sheet. It might look like this where I enter "1234" and the values "11" and "14" are drawn from the table above:
<TBODY>
</TBODY>
Similarly, if I were to enter "5678" the values "14" and "5" would be drawn from the table above:
<TBODY>
</TBODY>
My workbook is actually a bit more complex than is illustrated here, but I think this would work.
I'm thinking that I might need to create a pivot table in the Products sheet and then enter VB code into the Clients sheet referencing the pivot table created in the Products sheet. I think the code is to be typed into VBA after selecting "View Code" by right clicking the Clients tab, but I'm not exactly sure. And would I need to run a macro for it to update a change in the entered client number? Basically, I'm hoping that whoever can help me out on this one will take me from the beginning. I have been using Excel for a long time, so I'm otherwise handy in it, but I'm just now needing to take things a step further. And I'm guessing that this may simplify the structure of other things that I have worked on over the years.
Thank you in advance for your assistance!
Troy.
Sheet "Clients" lists the client account number, name, and other data.
Sheet "Products" lists each product owned by each client and includes account number, the type of product, and the value of the product.
All clients own multiple products of varying quantities, and generally they also own several products of each type, with each product owned listed in its own row such as this:
| Client Number | Product Name | Product Type | Quantity | Value |
| 1234 | ABC | 1 | 5 | 5.00 |
| 1234 | DEF | 1 | 3 | 6.00 |
| 1234 | GHI | 2 | 5 | 10.0 |
| 1234 | JKL | 2 | 2 | 4.0 |
| 5678 | ABC | 1 | 4 | 4.0 |
| 5678 | DEF | 1 | 5 | 10.0 |
| 5678 | GHI | 2 | 1 | 2.0 |
| 5678 | MNO | 2 | 6 | 3.0 |
<TBODY>
</TBODY>
In the "Clients" sheet I would like to be able to enter the account number in a cell and then in an array of other cells have the sum of each product type pulled out of the pivot table from the other sheet. It might look like this where I enter "1234" and the values "11" and "14" are drawn from the table above:
| Client Number: | 1234 |
| Sum of Value of Product Type 1: | 11 |
| Sum of Value of Product Type 2: | 14 |
<TBODY>
</TBODY>
Similarly, if I were to enter "5678" the values "14" and "5" would be drawn from the table above:
| Client Number: | 5678 |
| Sum of Value of Product Type 1: | 14 |
| Sum of Value of Product Type 2: | 5 |
<TBODY>
</TBODY>
My workbook is actually a bit more complex than is illustrated here, but I think this would work.
I'm thinking that I might need to create a pivot table in the Products sheet and then enter VB code into the Clients sheet referencing the pivot table created in the Products sheet. I think the code is to be typed into VBA after selecting "View Code" by right clicking the Clients tab, but I'm not exactly sure. And would I need to run a macro for it to update a change in the entered client number? Basically, I'm hoping that whoever can help me out on this one will take me from the beginning. I have been using Excel for a long time, so I'm otherwise handy in it, but I'm just now needing to take things a step further. And I'm guessing that this may simplify the structure of other things that I have worked on over the years.
Thank you in advance for your assistance!
Troy.
