Cannot change entries in spreadsheet part 2

[back2thefuture]

Following on from this thread:

https://www.excelforum.com/excel-general/1169716-cannot-change-entries-in-spreadsheet.html

Dear JeteMc and Jeff,

A million thankyou to you both. I think im almost there. Please see attached spreadsheet. i have a few things that i cannot get to work, please could you help? There are as follows:

1. 2. Corp Esc risk Log --> I have added initial/current/target columns. as shown below:

ini.JPG

When i make any changes to these values these are not reflected in in the dashboard as shown below:

inirisk.JPG

The same thing applies to the 4.AOR-Issue log. Any changes made are not reflected in the dashboard

Also these values are not then shown in the two graphs on the dashboard as shown below:

graph.JPG

2. In the graph below when i change the values in tab 2 or 4 they are not reflected in the table and subsequently not reflected in the grapgh below too.

2risk.JPG

3graph.JPG

[back2thefuture]

3. In tab 4.aor-issue log when i click on select i get a value of #N/A in the likelihood and impact columns. These should be blank when select is chosen. Only the risk rating column should contain N/A.

na.JPG

I would really appreciate if you guys could help. As promised im more than happy to contribute once done.

Thanks again

[back2thefuture]

Bump, anyone please?

[back2thefuture]

Any help at all please?

[JeteMc]

Here are formula modifications to G15:G17 respectively on the dashboard sheet:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Following these modifications as a template you could complete the Target and Current Risk Rating tables.
I don't see the problem with the Issue Type series on the Corporate Risk & AOR/Issue types chart, however the Risk Type series is trying to reference a group of cells that, while hidden by the chart, are blank.
I changed the formula for the AOR/Issue types table, cell D21 to read:

Please Login or Register  to view this content.

I made some changes to the series in the other graphs that will hopefully work once the formulas in Target and Current Risk Rating tables are corrected.
Let us know if you have any questions.

[back2thefuture]

Dear JeteMc,

Really appreciate your help on this
If you look at post #2 on this thread, that is the matrix that should have been used. I think you used the old one that i posted in the other thread.

The matrix in post #2 is what the changes need to be made to if you can please?

Thank you a million

[FDibbins]

instead of posting pics - which many members cannot see, upload a sample workbook please

[JeteMc]

I think that I have gotten all the table and chart formulas linked to the correct sheets and columns. The only things I am hesitant about are the three risk rating tables in columns C and D. The formulas all referenced the '2.Corp Escalation Risk Log' sheet column I and the values in those cells aren't numeric, so I doubt that is correct.
Let us know if you have any questions.

[back2thefuture]

Dear JetMc,

Once again thank you for all your help and hard work on this.
Could you please help with the three tables?

These are the points that need to be looked at please:

1. Three risk rating tables
2. The colours below for the tables in colums C & F should be as follows: Low = Green, Medium = Orange, High = Red.

1.JPG

3. In tabs 2 and 4 when you choose 'select' as shown below #N/A is shown in adjacent cells. N/A should only appear in the risk rating cell.

3.JPG

4.JPG

[back2thefuture]

4. if i make any changes to tab 4 as shown below, these are not reflected in the dashboard as shown below also.

5.JPG

6.JPG

5. No values are shown in the graphs below. These values should be taken from columns C & F on the dashboard tab. Also the high and medium variables are missing on both graphs.

7.JPG

So each graph for AOR-Issues and Corporate risks should include the low,medium, high risks in the same graph if that makes sense? or is there a better way to do it?

Many thanks again

[JeteMc]

To solve the #N/A issue on tab 4 I wrapped those formulas inside the IFERROR function, and then removed IFERROR from the risk rating column so the #N/A displays there.
Take a look and see if I have the tables and charts linked correctly.

[back2thefuture]

Dear JetMc,

You are AMAZING.
I think we are nearly there!

1. The colours in the graphs below do not currently correspond to the correct colous for each risk. So below a medium risk is shown as RED, where it should YELLOW. The high risks are shown as YELLOW and GREEN when they should be RED.

1.JPG

Or is it like this to differentiate between initial/target/current?

3. In tabs 2 and 4 there should be no # in N/A.

A million thanks JeteMc

[JeteMc]

The graph colors correspond to the table from which the values are taken i.e. initial, target or current. You can double click on any of the columns in the graph to bring up a window in which you can change the fill color.
Change the array entered formula in cell L2 of tab 2 to read:

Please Login or Register  to view this content.

Remember to simultaneously press Ctrl, Shift and Enter after the changes have been made. You can then double click the fill handle to have the formula copied down the column. Make the same change for all columns headed 'Risk Rating'.
Let us know if you have any questions.

[back2thefuture]

Hi JeteMc,

Thank you for this. But this is all greek to me!
I dont want to mess anything up since we are so close.
Would you mind doing it for me?

Many thanks

[JeteMc]

I changed the formulas on tabs 2 and 4. If you want the column colors changed you'll need to tell me what color you want to use for initial, for target and for current.

[back2thefuture]

Thank you Sir, this is amazing.
Initial = Yellow
Current = Purple
Target - Blue

[JeteMc]

Chart colors assigned. Let us know if you have any questions.

[back2thefuture]

Dear JeteMc,

Thank you again for for your amazing help on this.
Could you please look at the following minor issues:

1. In the risk codes tab i changed the original values for likelihood which were A,B,C,D.E to 1,2,3,4,5. I now get an error in tab 2. Please see below.

77.JPG

Also please could you change all the values in the drop down from A,B,C,D.E to 1,2,3,4,5 in both tabs 2 and 4?

In tab 4 now if i select a likelihood rating nothing happens?

Many thanks, updated spreadsheet attached. Please use this one

[JeteMc]

So that you'll know how to do this in the future, with H6 (on tab 2) selected, find and select Data Validation on the Data tab and change the values in the in the source window. I also changed the formula in H6 (tab 2) to read similarly to the formula in H12 on tab 4:

Please Login or Register  to view this content.

Let us know if you have any questions.

[back2thefuture]

Dear JeteMc,

Thank you again for all your help.
But now when choose a likelihood rating and an impact rating the risk rating is left blank? It should follow table 5 in tab 3. Also when you make any changes to the initial rating it populates the impact columns in current and target with #N/A? This should be the case. It should be blank.
These issues are replicated in tab 4 too

777.JPG

[Kamal Asharaf]

use iferror function, it may help.

[JeteMc]

Column A of table 5 needed to be changed from "A (certain)" etc. to "1 (certain)" etc. The array entered formulas for the risk ratings needed changing so that they would find the values of the text recovered by the LEFT function from column A of table 5. They now read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[back2thefuture]

Thankyou JeteMc,

The risk rating should follow table 5 in tab 3. So likelihood x consequence. But in the latest spreadsheet this does not follow. Please see below. The initial/current/target risk ratings should be: 16/16/8 not 8/8/4

84.JPG

Thank you again

We are almost there!

[JeteMc]

That being the case it would appear that instead of changing the values in column A of table 5 from A,B,C... to 1,2,3... (as in the file attached to post #22) they instead need to be changed them from A,B,C... to 5,4,3... Making that change yields the values stated in post #23
Let me know if you if you need to see my updated copy of the file.

[back2thefuture]

thanks JeteMc, please could you post the amended spreadsheet please?

many thanks

[JeteMc]

Here is the amended workbook with the conditional formatting rules applied.
Let us know if you have any questions.

[back2thefuture]

Dear JetMc,

Everything is PERFECT! you are AMAZING!
Just one thing and this was my mistake...

In tab 3 table 2 i changed the order from:

qqqqqqq.JPG

To

aaaaaaa.JPG

Now in the likelihood column in tabs 2 and 4 the description no longer shows up?

gggggg.JPG

Please could you help and sorry for my complete ineptitude!

Thank you again

[JeteMc]

I really can't tell you why the LOOKUP based formulas stopped working, however when replaced with VLOOKUP based formulas, as follows, they provide the expected results:

Please Login or Register  to view this content.

Let us know if you have any questions.

[back2thefuture]

Dear JeteMc,

How are you?
I hope you are well. Im hoping you can help me out please.

Two things if i may.

1. I added this graph to the dashboard:

aaa.JPG

The values on the x axis are taken from tab 4 cells L63, Q63, V63. The problem is that they are labelled 1,2,3 and i cannot seem to rename them?
1 should be labelled Initial risk rating total
2 should be labelled current risk rating total
3 should be labelled target risk rating total

2. In tab 4 cell L11, Q11, V11 i cannot sort by size?

Please find matrix attached. I really appreciate your help once more.

Thank you

[JeteMc]

I am doing well thanks, and hope that you are also.
I think that those issues are sorted out.
To get the axis labels I put them in cells H65:J65 on tab 4 then edited the chart axis to reference those three cells.
To get the table to sort and allow the chart to still work I had to do two things:
1) remove the merge and center in row 64 (of tab 4) and replace that with alignment across selection from the Number pane on the Home tab.
2) resize the table so that it only extends through row 63, otherwise when you sort a column (i.e. column L) either A to Z or Z to A row 64 gets moved and the values that create the columns on the chart are tied to the values in row 64.
Let us know if you have any questions.

[back2thefuture]

Thank you JeteMc, this is amazing...
Only one thing though, the filter works but when try to sort instead of getting sort by size (smallest to largest) for example i get sort A to Z?

ccccccccccccccccc.JPG

also when i sort it seems to put the smallest value at the top when i choose a to z, and when i sort z to a the values are at the bottom of the spreadsheet?

hope you can help.

thank you again

[JeteMc]

I'm not certain what "also when i sort it seems to put the smallest value at the top when i choose a to z" means. Sorting a to z means to put in smallest to largest order. As for the sort z to a issues, I assume that you mean that all of the N/A's are placed above the numeric values. My suggestion would be to filter those out when the z to a sort is in effect, you can always filter them back in when the sort a to z is applied. On these topics, Sorting and Filtering, I don't have as much experience as on other topics. It might be best to open a new thread addressing those questions in the 'Excel General' forum with the terms 'sort' and 'filter' included in the title to draw attention from members who have more experience with those topics.
Let us know if you have any questions.

[back2thefuture]

Hi JeteMc,

your suggestion makes sense - To untick the N/A field this works. many thanks.
Any idea why there is a number after each word here as shown below? I cannot delete these numbers either?
Many thanks as always

kkjjjj.JPG

[JeteMc]

Seems that is the protocol when that range of cells, $H$11:$V$63, becomes a Table. When I changed it back to a range I was able to delete the numbers, however as soon as I put the range back into a table format the numbers reappeared. That is a question that you may want to ask a broader audience to address.

[back2thefuture]

Thank you JeteMc, i will leave it as it is for now. Much appreciated.