August 12, 2022
I researched the Internet for assistance first, but couldn't find something to meet the need. So, I am hoping you folks can help.
The uploaded file is for part of a new dashboard. The goal of this part of the dashboard is to have the End User select a single Document Name from the drop-down in the "Dashbd Prep" tab. Then all document information about that Document Name populates in the columns from the raw data. The items in the columns will change as the end user selects different Document Names from the drop-down list.
This way, the End User sees all information in the columns about one Document Name that is listed multiple times in the raw data. This will help them to retrieve the particular document on a particulate date, from a particular box in storage, which is very useful and time-efficient in audit situations. They may need to filter to find document information quickly.
The Document Drop-down list ("Dashbd Prep" tab) has already been created with the use of a Pivot Table (see the "Pivot" tab). I noticed that when I tested adding a new Document Name, it automatically placed the new Document name at the bottom of the drop-down list in the "Dashbd Prep" tab. Is there a way that the drop-down list always remains in descending order when new items are added?
Please notice that not all of the columns from the raw data will be displayed on the dashboard. Also, the dashboard needs to remain as simple and straightforward as possible, which is why I am shying away from slicers. Additionally, if I made a slicer for the "Box" column, I would have to create a new slicer each time a new box is added to storage. I would prefer the dashboard updates without needing additional work. Keeping that in mind, how do the columns on the "Dashbd Prep" tab get populated dependent on what is selected in the drop-down list in B3?
If you have any questions or concerns, please do not hesitate to contact me.
July 16, 2010
Welcome to our forum!
You can and should use a PivotTable for this. See the Pivot sheet of the attached file for an example. You can place the document name in the filters area and the user can choose from the drop down.
If you want to add Slicers fort the box #, then you can and it will automatically pick up any new box numbers upon refresh of the PivotTable. No need to add more Slicers.
I hope that points you in the right direction.
August 12, 2022
I had almost given up on learning Pivot Tables. I couldn't figure them out, so I thought this problem had to all be solved with formulas or something else (I didn't know how to solve the problem). But you showed me the possibilities of Pivot Tables. Not only did you solve my problem, you have inspired me to delve into learning Pivot Tables to embrace their potential. Thank you for your awesome teaching and great website!
July 16, 2010
Great to hear, Karlin! Please consider my PivotTable Quick Start course, or if you want to dive deep, our Xtreme PivotTables course.