+ Reply to Thread
Results 1 to 11 of 11

Dashboard help! Data from columns showing in the correct boxes on dashboard

  1. #1
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Dashboard help! Data from columns showing in the correct boxes on dashboard

    Good morning all

    I have created a dashboard which will be printed every week and using Excel using the data refresh the data in tab 2 will update

    The spreadsheet has two tabs the first tab the dashboard, which I call the wall and the second the data.

    The data is really simple and don't need to do anything complicated with it just need it to pull through into the right boxes and that's where I need the help.

    For example in the data tab for Monique (Primary Owner, column D) I need the Definition, Aspiration and cost cell information from the row pulled through into the right location which is determined by the spin cycle stage (column B in data tab) on the wall

    In this instance Monique is in stage Build so I need the information from the Definition, Aspiration and costs to pull though to the first tab the wall and appear in the Build area of boxes, and not the others.

    I assume an index match would work for this?

    However, at some point the data tab will refresh once the data refresh is pressed and Monique will move from Build to Implemented so will need that information from Definition, Aspiration and costs to move down into the boxes in the Implemented area.

    Hopefully this makes sense.

    For now I just want to get that mechanism to work before looking and with more rows making other 3 work and see if this is possible.

    I'm pretty sure it is with the right formulas but this is where I need help.

    Hopefully, fingers crossed someone here can

    Thanks

    Matt
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    So I have made an update to this.

    The data the wall tab is looking at now a filtered data source so it will only pull through 1 category (column A in data tab in this instance IT Risks.

    I have called the wall IT Risks in tab one and managed to pull through all the information from the data into the wall.

    However the data is pulling through into 'all' boxes but as you can see the Definition, Aspiration & Costs its just the data repeated.

    Is there a way I can have it that based on the Spin cycle column in the data column B the wall will only show the information in that area. Basically hides the others from showing?

    I have highlighted in yellow the areas the text should appear in and the other boxes should just remain white.

    When the data changes in column B in the data tab for example to another stage the current stage showing in the wall should go white then it appear in the other area in the wall.

    Can anyone help?

    Hopefully this refinement should make it easier to work out.

    Thanks

    Attached Files Attached Files

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,758

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    Thou shalt not use merged cells for they are bad news. The only place you should use merged cells is for titles like in column B. Don't use them in column headers or any cells in which there will be formulas or cells that formulas reference. Sometimes they may work, but in general they cause a lot of problems. So if you want something "3 columns wide," make the cell three times wider. The same goes for row height.

    I redid the matrix so each "cell" is a real cell.

    Here is the magic formula each cell has a slight variation depending on the spin cycle and what column you are looking up.

    Here is a typical formula:
    =IF(ISNUMBER(MATCH(D$6,IF(Table_owssvr[Spin Cycle Stage]="Identified",Table_owssvr[Primary Owner],FALSE),0))=TRUE,INDEX(Table_owssvr,MATCH(D$6,Table_owssvr[Primary Owner],0),3),"")

    This is an array formula so confirm it with CTRL-SHIFT-ENTER.

    Let's tear it apart.
    MATCH(D$6,IF(Table_owssvr[Spin Cycle Stage]="Identified",Table_owssvr[Primary Owner],FALSE),0)

    this is really a simple match formula. It states find the row on whivh the owner's name is found, but only look at rows where the spin cycle stage is "Identified." The if statement to select the range in this part of the formula is what makes the formula an array formula.

    Match returns the number of the matching row if a match is found otherwise #N/A. So wrapping in in ISNUMBER returns True for a match and False if not found.

    If it is found, then do this: INDEX(Table_owssvr,MATCH(D$6,Table_owssvr[Primary Owner],0),3),
    Find the row with the owner and return the 3rd column (Definition).

    I did a little bit of testing and managed to move the results up and down depending on the spin cycle selected. I also added data validation for this item in the table since spelling is critical to the formulas.
    Attached Files Attached Files
    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.

  4. #4
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    Awesome this works exactly how I wanted it to, thanks dflak

    That formula really is magic and this is so cool as I can hit data refresh and the latest information pulls through and the dashboard changes so I just need to print and stick on the wall once a week - this is so cool

    Hopefully the data validation wont be needed as most of the selection's apart from Definition, aspiration and costs are drop down selections in SharePoint. Where possible I have let users select information because as you said spelling errors will break it haha

    Just one more thing and I don't know if this is possible, but is there a way to do a conditional format which makes the boxes light up (go in colour like the yellow) if information is showing in that box?

    That would just be a little cool effect to draw attention to those boxes in the stage it is in.

    Many thanks - wooooo

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,758

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    Just check to see if the length of the text in the cell is greater than zero.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    I would never of thought of =LEN(D17)>0 in conditional format.

    So is this basically saying if the length of the box is greater than nothing add the colour.

    Regards

    Matt

  7. #7
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    Oh no!

    I hit data refresh that pulled down the latest information from SharePoint which added changes and more rows to the data tab and then everything stopped working

    The middle name fields stop worked, and the information is not longer pulling into the squares.

    Attached Files Attached Files

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,758

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    If the data is going to be variable, we might have to do this in VB. I'm only about 8 hours behind today, so I'll look at it tomorrow.

  9. #9
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    Hi dflak

    Indeed it will be, the data tab (owssvr) is an extract from SharePoint which I will hide this tab when all working so no one can see.

    Once data refresh is selected the data tab will change depending on what the user has input into SharePoint.

    There will always only be 5 rows in the data so the dashboard will only ever have 5 IT Risks in it as an example.

    Once working of course I will then need to do add extra tabs each with is own data tab like the IT Risks.

    Once the IT Risks one works my plan was to learn, understand and use this new knowledge to replicate with the other tabs myself

    I really hope this makes sense

    Many thanks

    *Also just to note your help on this so far is really appreciated and loving learning along the way
    Attached Files Attached Files

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,758

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    I've had a chance to look at this, and need clarification on what the issue is. It should not matter how many rows there are, the table should take care of them. As long as the columns and the column names don't change, then you should be OK.

    So, make sure all the data is in the table, click anywhere in the table. This should make the Table Tools ribbon visible then click on design. On the far left is the table name and under that is a link to Resize Table. Click on this link and it shows the range of cells Excel thinks is in the table. It also puts a marquee around the range. If the table doesn't include everything you want, you can do a manual update as a one-time fix. Then check the process by which you add data to the table. If the rows are deleted properly (select rows and then Delete -> Table Rows) and you copy the new data into cell A2, then all the new data should become part of the table automatically. This is the default action. If this is not happening then there is a setting somewhere (I'd have to look around) that got reset to keep data from being automatically incorporated.

    As for the "middle named fields stop working check the Spin Cycle Stage names. Compare them to the formulas in the cells. For example: Table_owssvr[Spin Cycle Stage]="Identified" make sure the table data has "Identified" (check for trailing spaces). If it does not and this is the way it's being sent down, change the formula to match the data.

    Let's get this issue resolved first.

    I noticed that the names of the owners are formulas relating to the owssvr table (E.g., =owssvr!D2) - should this be like this or should it be a "manual entry" (recommend data validation).

    Will the owners be restricted to a specific Top 5 Category? In other words will Sarah always be working only IT risks? If this is so then the existing formulas will continue to work. If not (Sarah can also work other categories), then I will have to adjust the formulas to include Top 5 Category. I can do this, but it will take some concentration to get it right .

    So far, we can still stick with formulas and not need VBA.

    One final question: can an owner have more than one project in the same category? If so, then we will need to use VBA, which might actually be easier than the formulas .

  11. #11
    Registered User
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    98

    Re: Dashboard help! Data from columns showing in the correct boxes on dashboard

    Quote Originally Posted by dflak View Post
    I've had a chance to look at this, and need clarification on what the issue is. It should not matter how many rows there are, the table should take care of them. As long as the columns and the column names don't change, then you should be OK.

    The dashboard itself will never change there will always just be 5 people looking at 5 things say in IT Risks
    The same with the data, this table will not change the columns come from sharepoint so will not change

    So, make sure all the data is in the table, click anywhere in the table. This should make the Table Tools ribbon visible then click on design. On the far left is the table name and under that is a link to Resize Table. Click on this link and it shows the range of cells Excel thinks is in the table. It also puts a marquee around the range. If the table doesn't include everything you want, you can do a manual update as a one-time fix. Then check the process by which you add data to the table. If the rows are deleted properly (select rows and then Delete -> Table Rows) and you copy the new data into cell A2, then all the new data should become part of the table automatically. This is the default action. If this is not happening then there is a setting somewhere (I'd have to look around) that got reset to keep data from being automatically incorporated.

    As for the "middle named fields stop working check the Spin Cycle Stage names. Compare them to the formulas in the cells. For example: Table_owssvr[Spin Cycle Stage]="Identified" make sure the table data has "Identified" (check for trailing spaces). If it does not and this is the way it's being sent down, change the formula to match the data.

    Let's get this issue resolved first.

    I noticed that the names of the owners are formulas relating to the owssvr table (E.g., =owssvr!D2) - should this be like this or should it be a "manual entry" (recommend data validation).

    Users will be putting there names in SharePoint so the names will pull through to the data tab so thought might as well source the names from there instead of data entry. This spreadsheet I just want a user to press datafresh to pull the latest information down

    Will the owners be restricted to a specific Top 5 Category? In other words will Sarah always be working only IT risks? If this is so then the existing formulas will continue to work. If not (Sarah can also work other categories), then I will have to adjust the formulas to include Top 5 Category. I can do this, but it will take some concentration to get it right .

    Yes there is a chance Sara could be working on another category and not just on an IT Risk but this should not be an issue. Purely cause the data for IT Risks is pulled through on that data tab the other categories will have there own separate data tabs for them. Basically 4 categories means 4 dashboards and then 4 data tabs if that makes sense

    So far, we can still stick with formulas and not need VBA.

    One final question: can an owner have more than one project in the same category? If so, then we will need to use VBA, which might actually be easier than the formulas .
    Do you mean one person for example could be looking at 2 things out of a total of 5 in IT Risks? So they could have there name twice at the top, yes this is possible

    I was hoping for example in IT Risks that because the data that comes down is specifically IT Risks related meaning the data could easily be managed. for example the first row of data could always mean the first column in the dashboard. 5 rows only with 5 names (one person could be responsible for more than one item in the 5 under the category) but as the formula might be able to just look up that row only.

    If you have skype might be easier to talk it though maybe

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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