Creating a form based on cell values and predetermined layouts

[mummbles74]

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.

 

[HighAndWilder]

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.

 

[mummbles74]

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
	A	B	C	D	E	F
1	School	Test			Centre Number:	12345
2
3	Group	GM	Name	Play	Assessment	Character/DESIGN ASPECT
4	1	1-1	VIOLET HOLMES	TOO MUCH PUNCH	PERFORMER	MULTI ROLE
5	1	1-2	SAVANNAH SHEEPER JAMES	TOO MUCH PUNCH	PERFORMER	MULTI ROLE
6	2	2-1	ALIZA HUSSAIN	SHAKERS	PERFORMER	MULTI ROLE
7	2	2-2	OLIVIA NEWBOUND	SHAKERS	PERFORMER	MULTI ROLE
8	2	2-3	RUBY MISTRY	SHAKERS	PERFORMER	MULTI ROLE
9	3	3-1	ZOE DUNNELL	TWO	PERFORMER	MOTH/ROY
10	3	3-2	ISABELLA CONNOR	TWO	PERFORMER	MAUDI/LESLEY
11	4	4-1	ZEENA ALI-EMMA	PEOPLE PLACES AND THINGS	PERFORMER	EMMA
12	4	4-2	SHERYS MARIE	PEOPLE PLACES AND THINGS	PERFORMER	DOCTOR/MUM
13	5	5-1	AVA SMITH	TWO	PERFORMER	MOTH/MR IGER
14	5	5-2	EMILY RIAURDAN	TWO	PERFORMER	MAUDI/MRS IGER
15	6	6-1	MELODI CEMEL	CONSTELLATIONS	DESIGN	LIGHTING
16	6	6-2	ARIANNA ALEKSANDROVA	CONSTELLATIONS	PERFORMER	MARIANNE
17	6	6-3	GEORGE THOMPSON	CONSTELLATIONS	PERFORMER	ROLAND
18	7	7-1	KENNY NICHOLLS	TWO	PERFORMER	LANDLORD/ROY
19	7	7-2	LILY - LOUISE JOHNSON	TWO	PERFORMER	LANDLADY/LESLEY
20	8	8-1	NANA OAKYE	HARD TO SWALLOW	PERFORMER	MULTI ROLE
21	8	8-2	KATIE CAGGIANO	HARD TO SWALLOW	PERFORMER	MULTI ROLE
22	8	8-3	HARRY GARLAND	HARD TO SWALLOW	PERFORMER	MULTI ROLE
23	9	9-1	BORYS JANISZEWSKI	SMALL ISLAND	DESIGN	LIGHTING
24	9	9-2	LUKE HARTSTEAN	SMALL ISLAND	PERFORMER	BERNARD
25	9	9-3	DARCIE CLARK	SMALL ISLAND	PERFORMER	QUEENIE
Candidate List

Cell Formulas
Range	Formula
B4:B25	B4	=IF(ISBLANK(A4),"",(A4&"-"&COUNTIF($A$4:A4,A4)))

 

[mummbles74]

the above is the data and the below is the output format

Candidate Assessment sheet.xlsx
	A	B	C	D	E	F	G	H
1	School Name:	Test		Centre Number:		12345
2
3	Group Number:	9		Play:	SMALL ISLAND
4
5	Candidate Name		Exc. 9/10	Good 7/8	Reas. 5/6	Some 3/4	Ltd 1/2	Total
6	BORYS JANISZEWSKI
7	Role	DESIGN
8	LIGHTING
9	Appl of perf skills	Design Skills Evidence
10		Enhance mood and atmosphere
11		Changes/var.s complement perf
12	Interp & engagemnt	Interpretation of text
13		Comm. with aud sust.ing interest
14	C to perf	Indiv. contrib to performance
15	Add. notes
16
17
18	Candidate Name		Exc. 9/10	Good 7/8	Reas. 5/6	Some 3/4	Ltd 1/2	Total
19	LUKE HARTSTEAN
20	Role	PERFORMER
21	BERNARD
22	Appl of perf skills	Design Skills Evidence
23		Enhance mood and atmosphere
24		Changes/var.s complement perf
25	Interp & engagemnt	Interpretation of text
26		Comm. with aud sust.ing interest
27	C to perf	Indiv. contrib to performance
28	Add. notes
29
30
31	School Name:	Test		Centre Number:		12345
32
33	Group Number:	9		Play:	SMALL ISLAND
34
35	Candidate Name		Exc. 9/10	Good 7/8	Reas. 5/6	Some 3/4	Ltd 1/2	Total
36	DARCIE CLARK
37	Role	PERFORMER
38	QUEENIE
39	Appl of perf skills	Design Skills Evidence
40		Enhance mood and atmosphere
41		Changes/var.s complement perf
42	Interp & engagemnt	Interpretation of text
43		Comm. with aud sust.ing interest
44	C to perf	Indiv. contrib to performance
45	Add. notes
46
47
48	Candidate Name		Exc. 9/10	Good 7/8	Reas. 5/6	Some 3/4	Ltd 1/2	Total
49	EMPTY
50	Role	#N/A
51	#N/A
52	Appl of perf skills	Design Skills Evidence
53		Enhance mood and atmosphere
54		Changes/var.s complement perf
55	Interp & engagemnt	Interpretation of text
56		Comm. with aud sust.ing interest
57	C to perf	Indiv. contrib to performance
58	Add. notes
59
60
Sheet

Cell Formulas
Range	Formula
B1,F1	B1	='Candidate List'!B1
E3	E3	=VLOOKUP(A6,'Candidate List'!$C$3:$F$26,2,FALSE)
A6	A6	=L9
B7,B50,B37,B20	B7	=VLOOKUP(A6,'Candidate List'!$C$3:$F$26,3,FALSE)
A8,A51,A38,A21	A8	=VLOOKUP(A6,'Candidate List'!$C$3:$F$26,4,FALSE)
B9,B52,B39,B22	B9	=IF($B$7="performer",Performer!$B$9,Designer!$B$9)
B10,B53,B40,B23	B10	=IF($B$7="performer",Performer!$B$10,Designer!$B$10)
B11,B54,B41,B24	B11	=IF($B$7="performer",Performer!$B$11,Designer!$B$11)
B12,B55,B42,B25	B12	=IF($B$7="performer",Performer!$B$12,Designer!$B$12)
B13,B56,B43,B26	B13	=IF($B$7="performer",Performer!$B$13,Designer!$B$13)
B14,B57,B44,B27	B14	=IF($B$7="performer",Performer!$B$14,Designer!$B$14)
A19	A19	=L10
B31,E33,F31	B31	=B1
A36	A36	=L11
A49	A49	=L12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A35:H46	Expression	=$A$36="Empty"	text	NO
A48:H59	Expression	=$A$49="Empty"	text	NO