+ Reply to Thread
Results 1 to 15 of 15

Dashboard - Sipmle overview of multiple sheets with multiple items

  1. #1
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Dashboard - Sipmle overview of multiple sheets with multiple items

    I been googling and youtubing forever now feels like and I`d be really happy if anyone could help a newbie + user to make the right choices.
    First, some info regarding what I got:

    My sheet contains 10 sheets of data, a data sheet with formulas & connections and a dashboard sheet where I`d like to visualize the 10 sheets nicely, simple but informative. The reason why I have 10 sheets is due to every sheet holds a list of tag ids from different groups of valves i.e manual valves, Choke valves and so on.
    Every sheet can hold from 4 tags and upto 137 to the most. Therefor it makes it difficult to show a status for every tag id, but I guess im just thinking wrong since I know Excel is super smart and hope there is help in this super smart forum :D

    So, some more info regarding the info I want to visualize:
    Every tag ID in the different group(sheet) has 3 columns showing status with 4-5 different actions in a drop down menu function. I made an value column for each column to get a numeric value for the chosen action since pivot table only uses numeric values.

    And now, this is what its in my mind how Id like to visualize it all:

    1. 3 bar graph`s showing the overall status for all the groups. In detail; the 1st bar graph with 10 columns for every valve group to show the status of lets call it inspection. The next bar graph to visualize the status of action from the inspection and the last to show the activity of the action.
    2. I want also to make a line graph for all the valve groups to visualize the start date/status of the activity to see the overall progress of all the tag id`s from the groups.
    3. Last, I think, I guess I need several pie graphs(or other) to visualize every group by it self with the 3 different status columns in %.

    So, please. Can anyone help me with some tip and tricks here. I do know to make a pivot table for multiple sheets, I do know how to make all the charts, but I guess I dont know how to sort the values correctly in the Fiel list since it gets cludded with too much info when its sorted on tag IDs. I did try different options not sorting on the tag IDs but then it werent possible to refresh the data and the sliders didnt make it interactive..

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Welcome. You will find people more eager to jump on a project if a sample workbook is attached. Unfortunately the paper clip option doesn't work. So please follow the instructions below.

    I think once we see how the data is organized, we can help you better.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Thanks for your reply. I think I attatch the excel file + a picture file showing smt similiar dashboard I`d like. When I push manage attacthments they are there.
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    It is a very nice picture, but the specification is incomplete. For example, let's look at the pie chart. What do you mean by "for every sheet?" This implies that somewhere on the dashboard I tell it what sheet has the data and that all the charts on the dashboard are driven by the data on that sheet. Or do you want some charts to be driven by some sheets and other charts driven by other sheets?

    What data do you want plotted of each of the charts? For example, where do I get Sales by Category - what sheet and what range is plotted?

    I can see by the picture that some of these charts are actual charts, yet they are not included in the sample.

  5. #5
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Thank you for your reply. Im sorry for not being clear enough.
    The picture added is an example of how I`d like to visualize my workbook, and none of the charts are to be found in my workbook. Therefor, text, headlines of the charts etc is not relevant for my workbook.

    My workbook has 10 sheets. Each sheet consist of data which is made from different groups. You could say that sheet one is one category like carrot, another one orange, next apple and so on. I put each category of valves on a different sheet but it could be I would need to put all the data on a single sheet, and make an extra column telling what category each TAG ID is, if that would make it easier to handle the data?

    Regarding the Pie chart, I`d like to have one slider which has 1-10 option of which category of valve to be visualised in the pie to give status for ATS Status, Action or Activity. These 3 could also be on a slider. So you have 1 Pie chart, 1 slider where you chose the category, and then the last slider to show you what to visualize from ATS Status, Action or Activity.

    Hope this was better described?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    I'm getting the picture now. It appears that each sheet has the same data structure and what you want is to point the charts to the data on the appropriate sheet.

    I will ask your indulgence on one more item: on what columns would you like slicers for example ATS Responsible Company, ATS Status, Activity.

    I have a general idea on my approach. I am going to change the structure of the data, but I will keep the columns in the same order you have now and I will make the sheets look the same as they do now. I am going to convert them to Excel Tables and I am going to depend n a very strict naming convention. All this is behind the scenes. The sheets will still look the same and you should be able to enter data or paste data as you do now.

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Attached is a "proof of concept." I am not using a slicer to select the valve type. Slicers allow you to select more than one item. We wish to allow only one item. So I am using a simple drop-down, instead. I can use a slicer, but limit it to only the first item selected.

    I've converted each sheet to an excel table. When you select a value type, I copy the corresponding table to a sheet called "Current" - this is the sheet that will drive all the charts.

    What I need from you is what kind of metrics you would like to see. Take one of the sheets and create the kind of charts you want to see from that sheet. I can put them on the dashboard and link them to the current sheet.

    The main issue I see with the selected pie chart is the legend. It takes up way too much space. I don't know how to fix that.

    Is this concept what you had in mind?
    Attached Files Attached Files

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    This is about as far as I can take it for the moment. I can't figure out what else to measure and what else to filter on.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Wow, this is so nice of you, to help me, really apreciate that. So sorry for my late answers though, I been off work and with no internet since 17th of may.

    What you made is in the direction of what I want, yes. You managed to make the sorting of what sheet I want to display, some sliders and 2 pie`s. To answer your questions I would like to have sliders on : ATS Status in PH 1, Action PH 1, Activity PH 2.
    The other Pie you made for company can have a slider showing ATS Resp company for PH 1 and PH 2.

    Is it possible to get a interactive headline above the pie from the info you choose in slider? If I choose Completed it will be written Completed.
    The settings you made behind the scenes, is it difficult to learn? How can I display or see what you done to learn and understand? I been looking at the sheets you made, on the sheet Current, column P&Q, you marked it red. Why? In future when I have some data there I would like to make a time graph as mentioned, to visualize the overall progress of the valves. The Column R should have a function on the time graph giving a traffic light indicator when it gets close to specified date (yellow), and when it exceeds (red), and if its in good time x weeks (green).

    The metrics I need is partly what you made. I wanted a pie overview like you made, so that`s excellent, just needs some touch up and what I mentioned further up. Then I would need a bar graph showing all the groups in the same graph, each group having each it`s post in the bar. The metrics of this bar graph could be chosen in a slider with metrics like: ATS status and PH 2 Activity. This is so that I can visualize the whole project in a super quick look. For all the groups.

    I will push and look more on what you made, get to know it proparly and come back to you if there is smt more.
    Thank you so much.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    As for the interactive headline – which slicers do you want to show? Phase I TS Status only? Also, slicers can select multiple values. I can give you a comma separated list.

    I’ll explain how the program is put together.

    The key to the program is the Current page. On this page, is an Excel Table called Table_Current. The reason I use tables is because they “know” how big they are so pivot tables, formulas and charts that are built from them always reference the right amount of data regardless of how many rows there are in the table.

    Tables have a lot of advantages but they come at a cost: one of them being that each header has to be unique and it doesn’t tolerate merged cells. However, the same restrictions hold true for pivot tables, and since we are using slicers, we need pivot tables. So that is why I am not using your original headers.

    On the parameters sheet is a list of the sheet names. This is also an Excel Table. If you need to add a new sheet to track a new item, enter the name here. It will be picked up by the drop-down list on the Dashboard sheet automatically and the program will know about it.

    When you use the drop-down cell on the dashboard sheet, it fires off a change event. The code for this is on the sheet module for the sheet. This code clears out the contents of Table_Current, and copies in the contents of the table on the selected sheet.

    If you make a new sheet, you must adhere to the following naming conventions. Name the sheet x.y Name where there is a space between x.y and Name. Put the data into a table with the name Table_x.y.

    The pivot tables on the pivot sheet get their data from Table_Current regardless of what is currently copied in there. These pivot tables, in turn, drive the charts and the slicers.

    I do not see where I have anything marked in red in columns P&Q on the Current Sheet.

    Any work you do to format things like traffic lights, etc. Should be done in Table_Current. You can do the same thing for other sheets as well if you wish. When information is copied into Table_Current, it is copied in as values, so all the formats, conditional formats, etc. are maintained.

    You mentioned making a time-based chart based on future data. To do this, you may have to use another pivot table. There may be an issue with this if you make a chart. If the dates go down the columns then when you make a chart you get the items you are tracking as the X-axis and the dates become the “series.” There is an option to “swap” the axis but this changes the configuration of the pivot table. It will try to put the dates in the columns rather than the rows.

    If you run into this problem, It is solvabale. What needs to be done is overaly the pivot table with a named dynamic range (I have instructions for this) and use the named dynamic range to do the charting. The slicers should still work.

    As for the slicers. They are mostly intuitive. Select a cell in the pivot table and got to the Insert Ribbon and select Slicer. If you right click on a slicer, you get some options to configure it, and if you select Slicer Tools Option, you can select which reports the slicer controls. Do a web search to get more information.

    As for the other charts you’d like to see, if you can do a mock-up, I’ll see what I can do to emulate it.

    P.S. I added the Phase II Action Slicer.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Thank you again for your kind Help.
    Regarding the interactive headlines. Looking once more on the slicers now, they kinda work as headlines for the eie. Those marked Blue is what is Visual, as multiple choices as you made. So I Think Will just leave it like that. What I ment was that if you only had one option, the option you chose would be Visual as a headline above the Pia to make it more clear.

    Nice of you to share the background of how things are built up. Makes more sence for me now.
    I Will do a mock-up as I did from start, just more detailed for you and reply with attachment tomorrow regarding the bar Charts. It would be Great if you could Help me out with the time chart as mentioned. If you could just set some fictive dates to visualize it all, I would highly apreciate that.
    I have another spreadsheet showing that function I need there as well, so I Will take a screenshot from it and pass it on to you.

  12. #12
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    So, I attached a picture showing what I`m thinking regarding the time graph, smt similar like that or maybe you have a suggestion to visualize it easier and better. My main goal with this look is that I will get info from the ATS resp Company from column N regarding a date of delivery for the service given in column L. There is people at another location/department who will give me input of when they need that TAG, column A, which is Site need date in column R.
    Its very important for me to visualize and get a "alert" of which tags from column A, which cant get delivered within the site need date or is close to it (traffic light indicator). In the dashboard i would want a quick view which shows how many tags within the same group and system (column C), which is green, yellow or red. In the sheets 2.1-2.10 it would be nice that the tags got marked accordingly in the columns showing the dates, Q & R.
    Was that clear for you?
    PS: the picture is from google. So dont mind the data or text on them, but lets say each department is the valve groups. And the post in each graph is the tags from systems.
    Attached Images Attached Images

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Fill in some sample data. I'll still need to know what items to track.

    As for the charting, if you want to take a shot at it yourself, look at this site: https://peltiertech.com/Excel/Charts/.

  14. #14
    Registered User
    Join Date
    05-15-2018
    Location
    Norway, Stavanger
    MS-Off Ver
    2013
    Posts
    7

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    Need to track every single tag/item from column A from all sheets. Cause every valve will get a different delivery date, but the valve systems will be tested on a specific date, therefor the SND will be the same for one system group, seen in column C.

    Quote Originally Posted by dflak View Post
    Fill in some sample data. I'll still need to know what items to track.

  15. #15
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Dashboard - Sipmle overview of multiple sheets with multiple items

    I repeat, fill in some data and make a sample chart based on that data. I will take care of making it dynamic for you, but I will not make up data for you. I don't know enough about your business to be able to do this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup text in cells across multiple sheets to display on dashboard/overview sheet
    By lisajeanrieken in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-15-2016, 05:12 AM
  2. problem importing data from multiple sheets into single dashboard view
    By smulder1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2016, 12:33 PM
  3. [SOLVED] Macro to copy data from multiple sheets to overview and align results on overview sheet
    By McBree in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2015, 04:01 PM
  4. [SOLVED] Need corresponding hourly values (24per day) daily from multiple sheets in a dashboard she
    By arunjyoti.ec in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-02-2015, 04:50 AM
  5. Replies: 0
    Last Post: 10-24-2014, 06:51 AM
  6. [SOLVED] Counting multiple items with multiple criteria from multiple sheets?
    By essee in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-23-2013, 01:56 AM
  7. Replies: 9
    Last Post: 02-16-2009, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1