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.

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

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.

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 .

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

My mind must be running on low octane today. Could you provide some data that is NOT working.

I am doing this in my spare time at work and I don't have skype.

Hi Dflak

Basically the middle 2 owners are not pulling through on dashboard, cant work out why not.

Also when the spin cycle column in tab 2 changes (from dropdown update from user in SharePoint) they don't pull through onto the dashboard, even though tab 2 updates.

I think the issue here is that on tab 2 the column spin cycle stage works against a drop down you put in. So it works as long as the user changes the drop down in tab 2.

What happens is this information is linked to a Sharepoint list so if the user makes changes on Sharepoint and presses data refresh in excel the information pulls through and changes in the spin cycle stage column as its connected. Once this data refresh has happened the dashboard clears in tab 1 and does not up date.

Think it is a case of getting the dashboard to update against the information in the spin cycle stage column and not by clicking from the drop down.

Hope that make sense. This is like 90% there hehe, just need to work this off that column I think and workout why the name of people are not pulling through on dashboard.

Thanks Matt

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

ALL communication on this is to remain here in this thread, please. Thanks.

There were two things wrong with the file.

1. Some of the cells were formatted as text.

2. "Implemented" was spelled wrong in the formula.

I fixed both these issues. I added a helper column on the owssvr table that does a check to see if the imported data is spelled correctly. That was not the issue this time, but it would be nice to have this check.

Awesome that looks to work

So I added another Wall into this spreadsheet and called it Repeat Incidents and added the supported OWSSVR tab to it but this time its called OWSSVRA so has an extra letter on the end.

I then did the following:

1- Relinked the Owners to the OWSSVRA so =OWSSVRA!D2 as an example to pull through the names.

I checked that none of the cells were formatted as text, however D & J cells didn't pull through the names

****UPDATE**** I have solved the owners names now, I copy and pasted the cell for the second one and pasted which seemed to fix them

2- Link the squares in the wall to the new OWSSVRA table formulas.

It wouldn't let me update the tables to =IF(ISNUMBER(MATCH(D$6,IF(Table_owssvra[Spin Cycle Stage]="Identified",Table_owssvra[Primary Owner],FALSE),0))=TRUE,INDEX(Table_owssvra,MATCH(D$6,Table_owssvra[Primary Owner],0),3),"") as an example

Do you have any ideas as to why it wouldn't let me? I was hoping to copy what you did to the next tab and mirror the process.

Many thanks

Matt

When you copied Table_Owssvr to the new sheet, it got renamed to Table_Owssvr3 - Excel does that when you copy tables, it will add on an extra digit. Change the new table name from Table_Owssvr3 to Table_Ossvra and it should work. It worked for me when I tried it.

There were some instances where you did not have an array formula and other instances where you did not replace every instance of owssvr with owssvra.

If you need to make another page, duplicate the Repeat Incident sheet, then highlight all the cells with formulas. Press CTRL-H and replace owssvra with owssvrb or whatever. It should take a couple of seconds.

Hi dflak

Sorry to be a pain, I tried the above and highlighted the cells and pressed control and H but this just pulled up find and replace without anywhere showing formulas.

What I have done:

1. I put in all the walls and all the associated owssvr's in the spreadsheet.
2. Have changed the table names for the new owssvr's
3. Tried to add the checks to the owssvr's don't think it worked, columns L & O

I am just trying to work out how to link it which is confusing me

This is now the fully populated spreadsheet but walls not linked attached.

There is probably just a small step i'm missing to get this working.

Cheers

You have to type in what you want to find and replace manually.

Highlight the range D12:J44. Press CTRL-H. Fill in the dialog box as shown. Click on Replace all. You should get the message that there were 195 replacements.

To make the {} "unvanish, select the formula bar and confirm it with CTRL-SHIFT-ENTER. These are array formulas.

I believe I have solved all issues now.

To fix the above I did CTRL + F in the owner name and replaced = with = which fixed the name coming in

To ensure the dashboard worked I did CTRL + SHFT + ETR together which seemed to make it all work and information pull through.


I only have one remaining question as it all works.

I noticed that on a dashboard tab if a name was repeated or used more than once in an owner box, it would bring both information into the dashboard so a column could have 6 boxes light up instead of 3 as it recognizes the name.

Cheers

Matt

Try the following array entered formula as modeled for 'Definition':

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

It is easier to troubleshoot a formula if we can see a sample file that demonstrates the issue. That said you may want to make sure that there are no extra leading/following spaces when the word aspiration is placed in the Spin Cycle Stage column.
Also, I should have used a mixed reference to H6 and H9 so as to lock the row i.e. ...(Table_owssvra[[Primary Owner]:[Primary Owner]]=H$6)/(Table_owssvra[[Title]:[Title]]=H$9),1),3),"")
Let us know if you have any questions.

I had misread the Table_owssvra table. This regular formula works provided that in row 22, cost is changed to costing:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome and thank you for the feedback. To mark the thread as 'Solved' use the thread tools menu above the first post on this page. I hope that you have a blessed day.