Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with formula in an HR form
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 employees 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with formula in an HR form
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:B E$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 --- "Leslie" wrote: 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 employees 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Excel form (Formula?) | Excel Worksheet Functions | |||
Need Formula Help Form Table | Excel Worksheet Functions | |||
User Form Formula ? | New Users to Excel | |||
Formula for Order Form | Excel Discussion (Misc queries) | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) |