•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
In series drop down filters from multiple sheets
Our take
Are you feeling overwhelmed by the challenge of creating a cohesive dashboard from multiple sheets in Excel? You’re not alone. Many face the daunting task of pulling filtered data from extensive databases while ensuring that complex formulas remain intact. The goal is a streamlined dashboard with dropdown filters for locations and specific information, yet merging data without losing integrity can feel impossible. In this guide, we’ll explore practical solutions that can simplify your workflow and empower you to create an effective, user-friendly dashboard.
Portion rant and question. Unfortunately, being able to create pivot tables has earned me the title of "Excel Wizard" in my office, and I have been tasked with creating a dashboard to pull filtered data from several sheets easily. The database I am pulling from is fairly large, and outside of my abilities, YouTube and online searches are not getting me the exact answer I need.
In theory, the end result will be a dashboard with two dropdown filters. The first is to select the specific location (37 total), and the second is to select information from 10 separate sheets, like contact information, contract expirations, insurance policy information, equipment information, etc. They would also like each category to have its own sheet so the information can be looked at as a whole. I have pushed to have seperate excel files for each location with the information needed, but they want one place to view and edit all of the data.
The other caveat to this is that since pivot tables are a mind-boggling creation, I fear any complex formulas or functions may get damaged as they try to edit/update information in the data sheets.
My initial thought was to consolidate all of the information onto one sheet, but the different headers/information types stopped that plan quickly.
Besides advocating more for some type of software to store this information and accomplish this "dashboard" need, is there a solution to my problem?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating a graphic with custom located cells populated by a pivot tableI want to create a graphic with drop down menus that work off of each. So, I select the "Bore size" and the "Class". After I want custom placed cells to populate with the appropriate information based on a separate worksheet that has all of the information for each value. (First Picture Below) Red represents my drop down menus. Once I have populated that, I have a separate spreadsheet (pictured below) with the columns detailed for which variable they are to fill, and it will give me my values accordingly. What terms and capabilities with Excel should I be researching to be able to achieve this? I'm assuming "Pivot Tables" for referencing the information from Sheet 2 to Sheet 1, but I don't know how to get the information to place itself appropriately into the blue sections. Do I have to merge cells to fit there, or can I draw custom cell placement? TIA Also, does anyone know what you would call this type of chart in Excel? The best answer I have so far is "Lookup-Driven Dashboard". Sheet 1 https://preview.redd.it/o4ptbz0ayvmg1.png?width=1325&format=png&auto=webp&s=bddd06bc28023fba707942fd7bdade6341447e7d Sheet 2 https://preview.redd.it/3odfm92ryvmg1.png?width=472&format=png&auto=webp&s=a7c3c27880a79f63916ccc8ff5a1064458bcd239 submitted by /u/West-Doubt6824 [link] [comments]
- Excel Dashboard - InteractiveHi. I have to build an Excel dashboard to share with Senior Leadership. I can't build it in Power BI as licenses are limited and I am not allowed access to a workspace to publish for those without licenses. The data set is taken from our IT self service ticket system. I have it formatted as a table with a few helper columns to cleanse issues. I need to present info on tickets by: -directorate -divisions -ticket category -status (open or closed) -length of time open or by length time to resolve when closed. -It has to be visual and not just pivots so needs graphs etc. -It needs to be interactive in a way that the one workbook with the data and dashboard will be available to the Senior Leadership of each directorate or division, who need to be able to filter or use a slicer to amend the visuals for their own areas, and then go to the data set to see only those tickets. (1) i can create pivots tables and charts based on the table, and then slicers based on these, but they don't modify the table which is a requirement. (2) I can create slicers directly from the table which will modify it but then I can't connect these to a pivots table or chart to modify them on the dashboard itself. (3) this will be saved on sharepoint so I can't use any macros if they are a solution, and it has to be dummy proof - with all the goodwill in the world, someone will make a fuss if they can't see something or do something, and it will come back to bite me. Has anyone any ideas on how I can do this? I've tried searching for templates etc but I can't really work out the best way of doing this? I always thought i was pretty decent at Excel but dashboards are not something I would ever really create to be honest. Thanks in advance submitted by /u/liamlarmour [link] [comments]
- Consolidate data from different worksheets with final information coming from a combination of the worksheetsHoping for some help on a problem I can't wrap my head around. I need to consolidate some information from 12 different tabs (one data pull per month) into one worksheet with some of the data needing to be overridden and some needing to stay. With the most recent pull of the data not necessarily being the information I want showing, I'm not sure how to proceed. I'm trying to find a way to create this and pass it along to someone else to just load data and it automatically puts out the result I'm looking for. For instance, I pull information from my system in January for the entire year (broken into monthly buckets) and all of that is perfect. My second data pull of the year in February isn't so clean. I need to see the numbers from January on any of the January data (because it zeros out in February in my system) but everything related to February through December needs to be from the most recent data pull. Third pull of the year in March, I need to see the numbers from January and February for the January and February data and then need the new info for March through December. Basically, by the time December rolls around, I need data from all 12 sheets. Hopefully this makes sense. Any thoughts on how I can accomplish this in Excel? submitted by /u/megd726 [link] [comments]
- Multiple Tab Project Tracking DashboardI have an extremely large project with about 50+ same/similar program workflows. Each workflow is about a dozen different steps (i.e. Research, Revise Documents, Create work-orders, etc.) with some steps having multiple iterations (i.e. Revise Document 1, Revise Document 2) within a program/work flow. Each Step is organized by Worksheet tabs (i.e. a master document revision tab, a master work-order tab) with a column indicating what work flow/program its associated with, along with columns for preparer, reviewer, due date, status, etc.. What is the simplest way to slice and dice the data into a dashboard? Regular Pivot Table doesn't pull from multiple tabs, I can't seem to wrestle power pivot with many to many relationships, we've tried POWER Bi, and MS Lists with no luck. Ideally, I want a dashboard to filter by program/workflow, and see all steps, and iterations with owner, due date, etc. Looking for Suggestions. Bonus points if it can take a another slice by owner (across workflow, programs, iteration, prepare/reviewer) to essentially be an individual's personal action/assignment list. submitted by /u/couchfrog [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#google sheets#big data management in spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#natural language processing for spreadsheets#rows.com#digital transformation in spreadsheet software#large dataset processing#pivot tables#dashboard#dropdown filters#filtered data#multiple sheets#contact information#contract expirations