help creating a seating chart

[amazonmcguire]

Hello,
I was wondering if someone knew a way I could create a seating chart that fills x number of cells based on values from another sheet.. Using Excel 2003. Example sheet 1 would have class names and sizes. then sheet 2 would fill in the cells based on the class size.

Example sheet 1

Science class = 23 students
Art Class = 30 students
Music Class = 28 students
Math class = 22 students

Sheet 2 would have a seating template (for a school assembly) and would fill 23 cells with science (or a red colour or SC) then fill 30 cells with a blue colour or the word art etc...

 

[mmodroger]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Class	Students		Layout
2	Science class	23		1	2	3	4	5	6	7	8	9	10
3	Art Class	30		11	12	13	14	15	16	17	18	19	20
4	Music Class	28		21	22	23	24	25	26	27	28	29	30
5	Math class	22		31	32	33	34	35	36	37	38	39	40
6				41	42	43	44	45	46	47	48	49	50
7				51	52	53	54	55	56	57	58	59	60
8	Code	Range		61	62	63	64	65	66	67	68	69	70
9	SCI	1		71	72	73	74	75	76	77	78	79	80
10	ART	24		81	82	83	84	85	86	87	88	89	90
11	MUS	54		91	92	93	94	95	96	97	98	99	100
12	MAT	82		101	102	103	104	105	106	107	108	109	110
13	FREE	104
14				Seatings
15				SCI
16
17
18
19
20
21
22
23
24
25
Sheet3

Excel 2007

Cell Formulas
Range	Formula
B9	=SUM($B$2:C2)-B2+1
B10	=SUM($B$2:C3)-B3+1
B11	=SUM($B$2:C4)-B4+1
B12	=SUM($B$2:C5)-B5+1
B13	=SUM($B$2:C6)-B6+1
D15	=LOOKUP(D2,$B$9:$B$13,$A$9:$A$13)

Drag D15 down and right according to the layout. the rest you can adjust to your own seating layout. Just create the code table and input the formula in D15.