ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- Windows
Hello all!
I'm fairly new to Pivot Tables, and I'm having trouble figuring out how to do something. I initially built the table below to only have one set of values (Sum of Line Item Total"). I was going to put all of my other needed info (such as ("Sum of Quantity" & "Distinct Count Of Order ID") into separate tables on separate sheets. But it seems like there's no reason I can see why I shouldn't just add them into this same table. I am however, beginning to foresee a problem coming my way, so I wanted to stop before I go any further.
***Note: Not sure if it matters or not, but all of my query connections are connections only. All the data lives in folders on my desktop.
When I'm done building all of my Pivot Tables, I'm going to extract a bunch of info from all of the tables to build a nice, clean, printable summary dashboard (not referring to Pivot Table dashboard using Pivot Graphs/Charts, etc, but rather using just a standard Excel dashboard that I created). I am aware of the Getpivotdata feature which will come in very handy when trying to grab one cell's info, But how would I (using the screenshot below), for instance grab the total count of Distinct Order ID numbers for store 124? Or pull the same total but for store 128 (which is just off the screen further to the right), and always for "yesterday" no matter the date? It seems like when it comes time to pull that info, I'll need a way to first tell Excel to find the store number, and then once that happens proceed to find the associated Line Item Toal, Sum Of Quantity, etc. But being so used to vlookups and Index, my brain sees a problem with the store number being disjointed from the other column headers.
Hope my question makes sense. Thanks for any help, as I'm not having much luck finding an answer on my own.
I'm fairly new to Pivot Tables, and I'm having trouble figuring out how to do something. I initially built the table below to only have one set of values (Sum of Line Item Total"). I was going to put all of my other needed info (such as ("Sum of Quantity" & "Distinct Count Of Order ID") into separate tables on separate sheets. But it seems like there's no reason I can see why I shouldn't just add them into this same table. I am however, beginning to foresee a problem coming my way, so I wanted to stop before I go any further.
***Note: Not sure if it matters or not, but all of my query connections are connections only. All the data lives in folders on my desktop.
When I'm done building all of my Pivot Tables, I'm going to extract a bunch of info from all of the tables to build a nice, clean, printable summary dashboard (not referring to Pivot Table dashboard using Pivot Graphs/Charts, etc, but rather using just a standard Excel dashboard that I created). I am aware of the Getpivotdata feature which will come in very handy when trying to grab one cell's info, But how would I (using the screenshot below), for instance grab the total count of Distinct Order ID numbers for store 124? Or pull the same total but for store 128 (which is just off the screen further to the right), and always for "yesterday" no matter the date? It seems like when it comes time to pull that info, I'll need a way to first tell Excel to find the store number, and then once that happens proceed to find the associated Line Item Toal, Sum Of Quantity, etc. But being so used to vlookups and Index, my brain sees a problem with the store number being disjointed from the other column headers.
Hope my question makes sense. Thanks for any help, as I'm not having much luck finding an answer on my own.
