Creating a form based on cell values and predetermined layouts

mummbles74

Board Regular
Joined
Nov 14, 2009
Messages
125
Office Version
  1. 365
Platform
  1. Windows
Ok, I have been searching and searching for examples but struggling to find a suitable starting point.

May aim is as follows; I have a list of candidates in that table there is there Name (column A starting in row 3), Group number (Column B), Discipline (Column C).
The 2 Disciplines at the moment are Performer and Designer
For each discipline I have a sheet with an assessment template these sheets are names the same as the discipline

What I want to be able to do is fill out the table with the candidate details and then hit a generate button and it create an assessment template for group. The difficulty is that each group could have different numbers of candidate and each group could have a different number of Performers and Designers.

Eventually I would like for each candidates section to obviously reflect there role and have there name in a cell.

For clarity each of the templates for the different roles are laid out the same and are currently the same size. The name on every template would need to be entered in Cell A6 on the sheets. the layout for each assessment for ranges from A1:H16.

I appreciate this is quite vague but I am struggling to ascertain which way to go to be the most effective from the start.

I would be happy if a new sheet would be created for each group with the sections pasted in there.

Any help or points to get me started would be appreciated.
 
Ok, I have been searching and searching for examples but struggling to find a suitable starting point.

May aim is as follows; I have a list of candidates in that table there is there Name (column A starting in row 3), Group number (Column B), Discipline (Column C).
The 2 Disciplines at the moment are Performer and Designer
For each discipline I have a sheet with an assessment template these sheets are names the same as the discipline

What I want to be able to do is fill out the table with the candidate details and then hit a generate button and it create an assessment template for group. The difficulty is that each group could have different numbers of candidate and each group could have a different number of Performers and Designers.

Eventually I would like for each candidates section to obviously reflect there role and have there name in a cell.

For clarity each of the templates for the different roles are laid out the same and are currently the same size. The name on every template would need to be entered in Cell A6 on the sheets. the layout for each assessment for ranges from A1:H16.

I appreciate this is quite vague but I am struggling to ascertain which way to go to be the most effective from the start.

I would be happy if a new sheet would be created for each group with the sections pasted in there.

Any help or points to get me started would be appreciated.
Well, for a start it would be good to see what data you already have and what you want to produce. The latter can just be a mock up.

Use XL2BB to submit mini-sheets and or tables and these can be copied into a spreadsheet at our end.

This won't be very difficult based upon what you have explained.
 
Upvote 0
Apologies for the delay, I have been trying to get the outcome I need on my own and then get a better starting point. I have only been able to do it using formulas and conditional formatting and that is only because the assessment templates for the different roles are identical. It is something that I would like to push forward and the other parts will not be identical.

Candidate Assessment sheet.xlsx
ABCDEF
1SchoolTestCentre Number:12345
2
3GroupGMNamePlayAssessmentCharacter/DESIGN ASPECT
411-1VIOLET HOLMESTOO MUCH PUNCHPERFORMERMULTI ROLE
511-2SAVANNAH SHEEPER JAMESTOO MUCH PUNCHPERFORMERMULTI ROLE
622-1ALIZA HUSSAINSHAKERSPERFORMERMULTI ROLE
722-2OLIVIA NEWBOUNDSHAKERSPERFORMERMULTI ROLE
822-3RUBY MISTRYSHAKERSPERFORMERMULTI ROLE
933-1ZOE DUNNELLTWOPERFORMERMOTH/ROY
1033-2ISABELLA CONNORTWOPERFORMERMAUDI/LESLEY
1144-1ZEENA ALI-EMMAPEOPLE PLACES AND THINGSPERFORMEREMMA
1244-2SHERYS MARIEPEOPLE PLACES AND THINGSPERFORMERDOCTOR/MUM
1355-1AVA SMITHTWOPERFORMERMOTH/MR IGER
1455-2EMILY RIAURDANTWOPERFORMERMAUDI/MRS IGER
1566-1MELODI CEMELCONSTELLATIONSDESIGNLIGHTING
1666-2ARIANNA ALEKSANDROVACONSTELLATIONSPERFORMERMARIANNE
1766-3GEORGE THOMPSONCONSTELLATIONSPERFORMERROLAND
1877-1KENNY NICHOLLSTWOPERFORMERLANDLORD/ROY
1977-2LILY - LOUISE JOHNSONTWOPERFORMERLANDLADY/LESLEY
2088-1NANA OAKYEHARD TO SWALLOWPERFORMERMULTI ROLE
2188-2KATIE CAGGIANOHARD TO SWALLOWPERFORMERMULTI ROLE
2288-3HARRY GARLANDHARD TO SWALLOWPERFORMERMULTI ROLE
2399-1BORYS JANISZEWSKISMALL ISLANDDESIGNLIGHTING
2499-2LUKE HARTSTEANSMALL ISLANDPERFORMERBERNARD
2599-3DARCIE CLARKSMALL ISLANDPERFORMERQUEENIE
Candidate List
Cell Formulas
RangeFormula
B4:B25B4=IF(ISBLANK(A4),"",(A4&"-"&COUNTIF($A$4:A4,A4)))
 
Upvote 0
the above is the data and the below is the output format

Candidate Assessment sheet.xlsx
ABCDEFGH
1School Name:TestCentre Number:12345
2
3Group Number:9Play:SMALL ISLAND
4
5Candidate NameExc. 9/10Good 7/8Reas. 5/6Some 3/4Ltd 1/2Total
6BORYS JANISZEWSKI
7RoleDESIGN
8LIGHTING
9Appl of perf skillsDesign Skills Evidence
10Enhance mood and atmosphere
11Changes/var.s complement perf
12Interp & engagemntInterpretation of text
13Comm. with aud sust.ing interest
14C to perfIndiv. contrib to performance
15Add. notes
16
17
18Candidate NameExc. 9/10Good 7/8Reas. 5/6Some 3/4Ltd 1/2Total
19LUKE HARTSTEAN
20RolePERFORMER
21BERNARD
22Appl of perf skillsDesign Skills Evidence
23Enhance mood and atmosphere
24Changes/var.s complement perf
25Interp & engagemntInterpretation of text
26Comm. with aud sust.ing interest
27C to perfIndiv. contrib to performance
28Add. notes
29
30
31School Name:TestCentre Number:12345
32
33Group Number:9Play:SMALL ISLAND
34
35Candidate NameExc. 9/10Good 7/8Reas. 5/6Some 3/4Ltd 1/2Total
36DARCIE CLARK
37RolePERFORMER
38QUEENIE
39Appl of perf skillsDesign Skills Evidence
40Enhance mood and atmosphere
41Changes/var.s complement perf
42Interp & engagemntInterpretation of text
43Comm. with aud sust.ing interest
44C to perfIndiv. contrib to performance
45Add. notes
46
47
48Candidate NameExc. 9/10Good 7/8Reas. 5/6Some 3/4Ltd 1/2Total
49EMPTY
50Role#N/A
51#N/A
52Appl of perf skillsDesign Skills Evidence
53Enhance mood and atmosphere
54Changes/var.s complement perf
55Interp & engagemntInterpretation of text
56Comm. with aud sust.ing interest
57C to perfIndiv. contrib to performance
58Add. notes
59
60
Sheet
Cell Formulas
RangeFormula
B1,F1B1='Candidate List'!B1
E3E3=VLOOKUP(A6,'Candidate List'!$C$3:$F$26,2,FALSE)
A6A6=L9
B7,B50,B37,B20B7=VLOOKUP(A6,'Candidate List'!$C$3:$F$26,3,FALSE)
A8,A51,A38,A21A8=VLOOKUP(A6,'Candidate List'!$C$3:$F$26,4,FALSE)
B9,B52,B39,B22B9=IF($B$7="performer",Performer!$B$9,Designer!$B$9)
B10,B53,B40,B23B10=IF($B$7="performer",Performer!$B$10,Designer!$B$10)
B11,B54,B41,B24B11=IF($B$7="performer",Performer!$B$11,Designer!$B$11)
B12,B55,B42,B25B12=IF($B$7="performer",Performer!$B$12,Designer!$B$12)
B13,B56,B43,B26B13=IF($B$7="performer",Performer!$B$13,Designer!$B$13)
B14,B57,B44,B27B14=IF($B$7="performer",Performer!$B$14,Designer!$B$14)
A19A19=L10
B31,E33,F31B31=B1
A36A36=L11
A49A49=L12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A35:H46Expression=$A$36="Empty"textNO
A48:H59Expression=$A$49="Empty"textNO
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top