Here's what I want to do:
I have a table in Excel with all the records I need. I want the user to select (filter) a pivot table for a particular merchant ID. The pivot table then populates with the summarized information for that ID, including states and counties where the merchant does business.
What I would like to happen then is for the user to be able to press a button creating another pivot table in another worksheet that shows the same summarized information for all the competitors of the selected merchant in the same states and counties in which the selected merchant operates. (There are other fields as well such as numbers of members, visits to stores, etc. that I also need to see in the pivot tables.)
I think this can be done in Access by passing the selected merchant ID to a pivot table form's recordsource SQL statement but I haven't tried it yet. My question is if this can be done in Excel somehow. I'd prefer to do it in Excel since the pivot tables in Access don't seem to be that great; or, at least, not as great as they are in Excel. I'm using Excel 2010.
Thanks for your help.
I have a table in Excel with all the records I need. I want the user to select (filter) a pivot table for a particular merchant ID. The pivot table then populates with the summarized information for that ID, including states and counties where the merchant does business.
What I would like to happen then is for the user to be able to press a button creating another pivot table in another worksheet that shows the same summarized information for all the competitors of the selected merchant in the same states and counties in which the selected merchant operates. (There are other fields as well such as numbers of members, visits to stores, etc. that I also need to see in the pivot tables.)
I think this can be done in Access by passing the selected merchant ID to a pivot table form's recordsource SQL statement but I haven't tried it yet. My question is if this can be done in Excel somehow. I'd prefer to do it in Excel since the pivot tables in Access don't seem to be that great; or, at least, not as great as they are in Excel. I'm using Excel 2010.
Thanks for your help.
