Ping Max

[Me]

Hi Max - couldn't resist using time I don't really have to play with your
offering on the risk assessment sheet. Could I ask one question about that,
and then explain one more step that's needed.

The question is - is there any way to have the risk level and the score set
to blank at the point when the file is opened, rather than seeing whatever
was the last assessment saved?

The next step with the form is that there are then a further four questions
with yes/no answers. If there are 2 or 3 "yes" answers, then the risk level
is bumped up by one (unless it is already at the maximum of very high). If
there are 4 "yes" answers then the risk level is bumped up by two levels, or
to the maximum if it was already high. So it needs to read the score from
the first part and work on from there.

Many thanks for your time.

John

 

[Max]

For previous background to this post,
see : http://tinyurl.com/3586n

Tinker around with these additional set-up steps
----------------------------
In Sheet1
-----------
Put in F6: Very High
(The above is to help simplify the criteria for the
revised formulas in Sheet2)

In Sheet2
-------------
List down in C8:C11 : Q4,Q5,Q6 and Q7

Select D811
Click Data > Validation
Under "Allow:", select List
In "source:" box, put: Yes,No
Click OK

(The above creates the 4 DV drop lists for the additional
questions Q4 to Q7)

Put in C14:
=IF(OR
(B9="",B10="",B11="",D8="",D9="",D10="",D11=""),
"You must
answer ALL Questions 1 to 7", OFFSET(Sheet1!
$A$1,MATCH(B9,Sheet1!A:A,0)-1,MATCH(A9,Sheet1!1:1,0)-1)+
OFFSET(Sheet1!$A$1,MATCH(B10,Sheet1!A:A,0)-1,MATCH
(A10,Sheet1!1:1,0)-1)+ OFFSET(Sheet1!$A$1,MATCH(B11,Sheet1!
A:A,0)-1,MATCH(A11,Sheet1!1:1,0)-1))

Put in C15:
=IF(ISTEXT(C14),"-",OFFSET(Sheet1!$G$1,MATCH(C14,Sheet1!
G:G,1)-1,-1))

Mask C15 by formatting the font colour for C15 the same as
the fill colour

Put in B14:
=IF(OR(B9="",B10="",B11="",D8="",D9="",D10="",D11=""),"-",
IF(AND(COUNTIF($D$8:$D$11,"Yes")>=2,COUNTIF
($D$8:$D$11,"Yes")<4, C15<>"Very High"),
OFFSET(Sheet1!$F$1,MATCH(C15,Sheet1!F:F,0),,), IF(AND
(COUNTIF($D$8:$D$11,"Yes")=4,C15<>"Very High"),OFFSET
(Sheet1!$F$1,MATCH(C15,Sheet1!F:F,0)+1,,),C15)))

Select B14:C14

Click Format > Conditional Formatting

Make the settings:
Condition 1
-------------
Formula Is |
=AND(ISBLANK($B$9),ISBLANK($B$10),ISBLANK($B$11),ISBLANK
($D$8),ISBLANK($D$9),ISBLANK($D$10),ISBLANK($D$11))

Click Format button and format the font color (Font tab)
to the same colour as the cell fill colour (e.g.: 'white")

Click OK

(The above will mask the the contents in B14:C14
if the range B9:B11 and D811 are blank)

----
Now let's put a "Reset" button in Sheet2

Press Alt + F11 to go to VBE
Click Insert > Module

Copy > Paste the sub Reset() below - viz.
everything within the dotted lines
from "begin vba" till "end vba"
into the empty white space on the right side in VBE

---------begin vba----------
Sub Reset()
Sheets("Sheet2").Range("B9:B11,D811").ClearContents
Range("A1").Select
End Sub
---------end vba----------

Press Alt + Q to exit and return to Excel
------------
In Sheet2
------------
Draw a button on the sheet from the Forms Toolbar
(If necessary, activate the Forms toolbar via View >
Toolbars > Forms)

The Assign Macro dialog will pop-up automatically
[You can also get at this dialog by right-clicking on an
existing button > Assign Macro]

Select "Reset" > OK

Rename the button to taste,
e.g.: "Reset" (but of course!)

Clicking on the button will run the macro,
which resets / clears the range: B9:B11 and D811

---
John, I'll be sending the revised sample workbook
with the above construct to your id ..

Anyone interested in a copy,
just post a "readable" email here.

 

[Me]

Max, you are now voted superstar - I really didn't expect you to just do it
all, at best I thought I'd get pointers to get started from. I spent time at
work today going through the structure of the first part that you sent, as a
learning exercise to work out how to approach the rest. Just got home and
checked mail, and there was the whole solution. You've saved me hours - but
I will still go through it to try and learn ready for whatever other
processes would benefit from computerisation.

I'm sure that some of my colleagues who use the paper form will want
copies - so
be asured I will give you well-due credit.

Many many thanks

 

[Max]

You're welcome !
Pleased to help

Thanks for the feedback