Formula help

  • Thread starter Thread starter DaveAsh
  • Start date Start date
D

DaveAsh

Hi,
I am setting up a checksheet for an auditor and I am struggling with one
particular function. The checks that are carried out are marked yes/no for
any action needed. So for example, Is all relevant info placed on notice
boards is written in A1 and 'yes' or 'no' in cell A2
On another tab named 'action plan' all of the major sections within the
spreadsheet are listed with the number of yes's flagged. I need a formula
that inputs the checks that are marked 'yes' for action. (As explained in the
above example)

Is this possible? and if so how would I go about it?

Cheers
 
There may be better ways but try this:

With the questions in Sheet1 from A2 down and the Yes/No's from B2 down,
enter the Actions from C2 down and then hide column C.

In Sheet2 A2 array enter (by pressing Ctrl + Shift + Enter not just Enter):

=IF(ISERROR(SMALL(IF((Sheet1!$B$2:$B$120="Yes"),ROW($A$1:$A$119)),ROW()-ROW($A$1))),"",INDEX(Sheet1!A$2:A$120,SMALL(IF((Sheet1!$B$2:$B$120="Yes"),ROW($A$1:$A$119)),ROW()-ROW($A$1))))

Drag across to C2 on the fill handle and then down to Row 120

Any Question with a Yes in Column B in Sheet1 will appear in a list on
Sheet2 with the Action in Column C

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I'm not really sure about your data layout, but you could use the
COUNTIF function:

=COUNTIF(range,"Yes")

to count all the Yes values in the range.

Hope this helps.

Pete
 
Send me an email so that I can get your address and I will send you a small
denonstration workbook.

Change my address as it says in the signature below.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top