Hi everyone,
Happy new year.
Im still stuck on this.
Its driving me nuts!!!
Is there any way i can edit/add to this...so for example i need to add a new risk/issue type so it shows up on the dashboard...But i cant?
Please can anyone help.
File is located below.
Any help gratefully received.
Many thanks
Last edited by back2thefuture; 01-13-2017 at 06:43 PM.
Hello & Welcome to the Forum (and Happy New Year),
There are a fair amount of folks who do not want to click on a site outside of EF to look at an attachment.
You might want to attach your sample here...
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
HTH
Regards, Jeff
jeffreybrown,
Thanks Jeff, apologies i have followed your instructions now
Last edited by jeffreybrown; 01-13-2017 at 06:56 PM. Reason: As per Forum Rule #12, please don't quote whole post unless necessary -- it's just clutter.
Are you referring to the drop down in B6?
If so, select B6 and then on the Ribbon...
Data >> Data Tools >> Data Validation >> Source: Add your additional requirements >> Check Apply these changes to all other cells with the same settings
If you now have to add it to the dashboard you will have to add it manually as everything else on the dashboard has been entered manually
Hi Jeff,
Thank you for this. That really helped.
But i have another request please and i will try to explain.
Im attaching two spreadsheets, matrix 1 and matrix 2. Matrix 1 is my almost complete spreadsheet. If you look at tab no.3 and table 5 i somehow need the formulas to work in tab no.2.
Right now you can see the original table 5 in matrix no.2. It follows the key E,H,M.L and these are shown in table 5.
But i need it to follow the key in matrix no.1 which is L,M,H and you can see in table 5 the letters are replaced by the numbers.
So you look at tab no.2 the final risk rating should be a number and not a letter as it currently is, based upon the likelihood and impact/consequence from tab no.3 table 5.
I would really appreciate any help, and im willing to contribute if necessary.
Many thanks
I'm sorry but this has me lost. If Matrix 2 is not necessary for answering the question, please do not include it. It seems to be adding some confusion for me.
I see the numbers in the tab, 3.Risk Codes, but not following the logic of where you need a formula.
So this is the table with the risk numbers:
Table 5.JPG
This is the old table with the letters:
Table 5 old.JPG
You can see that these letters are part of a formula that is outputted here on the risk log tab
old letters.JPG
I need to replace the letters with the numbers.
For example the cell K6 in the risk log tab shows this formula:
=IF(ISNUMBER(FIND($G6&$I6,"A1 A2 B2 B3 C3 D4 E4 E5",1)),"H",IF(ISNUMBER(FIND($G6&$I6,"B1 C2 D3 E3",1)),"M",IF(ISNUMBER(FIND($G6&$I6,"C1 D1 D2 E1 E2",1)),"L",IF(ISNUMBER(FIND($G6&$I6,"A3 A4 A5 B4 B5 C4 C5 D5",1)),"E","N/A"))))
Hope you can help
Many thanks
Maybe this is what you seek...
Look at I6, K6, and L6 on the 2.Corp Escalation Risk Log tab
Part of the fix had to do with removing trailing spaces from words you are looking up. I fixed them on this sheet, but you'll have to go thru your sheet if that's what you are going to use.
Hi Jeff,
This is great!!! How did you do it?
Would it be possible that when you make the changes in 2.Corp Escalation Risk Log tab that the cells H,I,J,K.L change colour to reflect the risk rating as shown below?
Lastly on 4.AOR-issue Log
key.JPG
A million thanks
Last edited by back2thefuture; 01-15-2017 at 05:37 PM.
On tab 2.Corp Escalation Risk Log, try in this order...
Highlight range >> $H$6:$L$57
Condition 1: =$L6<=5
Condition 2: =$L6<=15
Condition 3: =$L6<=25
Check Stop if True on all the conditions
Hi Jeff,
Many thanks for this...But im not sure how to do this
Please can you help?
Many thanks
The conditional formatting rules have been set up as per Jeffery Brown's instructions.
Note the rules that were previously applied to columns H:L of Corp Escalation Risk Log have been removed so that you can see how Jeffery Brown's rules work.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
@Thank you JeteMc for helping out.
@b2tf,
There are tons of tutorials on line to help you out. Here's just one Conditional Formatting
Conditional Formatting
- Highlight applicable range >> H6:L57
- Home Tab >> Styles >> Conditional Formatting >> New Rule
- Select a Rule Type: Use a formula to determine which cells to format
- Edit the Rule Description: Format values where this formula is true: =$L6<=25
- Format… [Number, Font, Border, Fill] Select Fill >> Red
- OK >> OK
- Stop if True
- New Rule...
- Select a Rule Type: Use a formula to determine which cells to format
- Edit the Rule Description: Format values where this formula is true: =$L6<=15
- Format… [Number, Font, Border, Fill] Select Fill >> Yellow
- OK >> OK
- Stop if True
- New Rule...
- Select a Rule Type: Use a formula to determine which cells to format
- Edit the Rule Description: Format values where this formula is true: =$L6<=6
- Format… [Number, Font, Border, Fill] Select Fill >> Green
- OK >> OK
- Stop if True
- OK
When you are done with the steps above, your conditions should be Green, Yellow, followed by Red.
Last edited by jeffreybrown; 01-16-2017 at 12:02 PM.
Sorry messsed up
Last edited by back2thefuture; 01-16-2017 at 01:37 PM.
For some reason your attachments are not viewable...
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Cant post!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks