+ Reply to Thread
Results 1 to 35 of 35

Cannot change entries in spreadsheet part 2

  1. #1
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Cannot change entries in spreadsheet part 2

    Following on from this thread:

    http://www.excelforum.com/showthread.php?t=1169716

    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

  2. #2
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    Bump, anyone please?

  4. #4
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    Any help at all please?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    Here are formula modifications to G15:G17 respectively on the dashboard sheet:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    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:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Cannot change entries in spreadsheet part 2

    instead of posting pics - which many members cannot see, upload a sample workbook please
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  10. #10
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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:
    Formula: copy to clipboard
    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.

  14. #14
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

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

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

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

  18. #18
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  21. #21
    Registered User
    Join Date
    06-12-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Cannot change entries in spreadsheet part 2

    use iferror function, it may help.

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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!

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.
    Attached Files Attached Files
    Last edited by JeteMc; 01-20-2017 at 02:54 PM. Reason: Added .xlsx file

  25. #25
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

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

    many thanks

  26. #26
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

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

  27. #27
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  28. #28
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files

  29. #29
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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
    Attached Files Attached Files

  30. #30
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  32. #32
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.

  33. #33
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

    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

  34. #34
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Cannot change entries in spreadsheet part 2

    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.

  35. #35
    Registered User
    Join Date
    12-14-2011
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Cannot change entries in spreadsheet part 2

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

+ 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] Cannot change entries in spreadsheet
    By back2thefuture in forum Excel General
    Replies: 15
    Last Post: 01-16-2017, 01:51 PM
  2. Displaying part of a spreadsheet on a VBA form
    By cmurda in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2014, 08:45 PM
  3. Sorting only includes part of my spreadsheet
    By Judes in forum Excel General
    Replies: 9
    Last Post: 09-15-2014, 12:01 AM
  4. Inserting a picture from one part of a spreadsheet to another
    By glass-pumpkin in forum Excel General
    Replies: 2
    Last Post: 03-05-2012, 01:33 PM
  5. How do I change multi-line entries to single line entries in Exce.
    By CPOWEREQUIP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-13-2005, 08:06 PM
  6. SumIf won't work on part of spreadsheet
    By cybercab in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2005, 08:01 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