HELP with formula in an HR form

[Leslie]

In our employee performance evaluation workbook, page 1 (the Leadership
Talent Assessment worksheet) contains a list of nine (9) performance
attributes.

Managers must rate an employee’s performance of each attribute as “Yesâ€,
“No†or “In Part†by placing an “x†in a box next to the appropriate rating
level.

If the manager types an “x†in the box next to “No†or “In Partâ€, the name
of that performance attribute needs to display in one of four (4) fields on
page 3 (the Performance Plan worksheet). The Performance Plan has spaces for
four (4) attributes needing further development or improvement. If an “x†is
typed into the box next to “Yesâ€, nothing needs to happen.

Please help me create a formula that tells the first field on the
Performance Plan to pull the name of performance attribute #1 if an “x†is
typed into the box for “No†or “In Partâ€, and if not, then go to performance
attribute #2, and pull the name of performance attribute #2 if an “x†is
typed into the box for “No†or “In Partâ€, and if not, go to performance
attribute #3, etc. The formula needs to tell the first field to keep going
down the list of performance attributes looking for an “x†next to “No†or
“In Partâ€.

For the second field on the Performance Plan, I need that field to look at
the first field and if there is data in the first field, the second field
needs to start the process all over again with performance attribute #2. I
will need to repeat this formula in fields 3 and 4 also.

Here is a link to my file: http://freefilehosting.net/download/433hm

Can you help me with this functionality, please? THANK YOU!

 

[Max]

Here's my offer for you, Leslie

Illustrated in this implemented sample:
http://freefilehosting.net/download/434b8
Automated Competency Assmt-Devt Plan.xls

(Remember to press the YES button below, won't you?)

Construct
In sheet: Individual Development Plan,
First, remove all merged cells in rows 13 down

In an adjacent area to the right (say in cols BB to BE which will be hidden
away)
Hardwire it with simple link formulas pointing to the various "In Part, No"
cells in "Leadership Talent Assessment" within BB13:BC21
eg, for Accountability, in BB13:BC13
='Leadership Talent Assessment'!AD26
='Leadership Talent Assessment'!AD28
and so on, for all 9 competency attributes

Then place in BD13: =IF(COUNTIF(BB13:BC13,"x"),ROWS($1:1),"")
Copy down to BD21

List the 9 corresponding competency attributes in BE13:BE21, ie:
Accountability
Business Acumen
Communication
Customer Focus
Decision Making
Leadership
Results Orientation
Teamwork
Core Values

Then place in A13
=IF(ROWS($1:1)>COUNT(BD$13:BD$21),"",INDEX(BE$13:BE$21,SMALL(BD$13:BD$21,ROWS($1:1))))
Copy down to A21. This will return the exact automated results that is
sought, with all competencies requiring development neatly bunched at the
top. Then add a nice simple dash of CF which is pegged to the possible
returns in A13:A21. Select A13:AZ21, apply a normal fill of gray. Then select
A13:AZ21 (with A13 active), apply CF using Formula Is: =$A13<>"". Format it
with white fill > Ok out.

Test it out. It'll function exactly as required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---