Seating Chart

[jschping]

Hi,

I want to make a seating chart report for my student's finals testing.

I have an Excel spreadsheet with each student's name, their room number, row
number, seat number.

I want to make a chart that is a visual aid to where they sit. So I don't
jsut want a table listing of names and rows, I want it to come out in the
rows and in the right order. It doesn't have to be spectacular, but it should
be a visual aid.

For example:

------------------------------ROOM 201

ROW 1------------ROW 2------------- ROW 3--------------- ROW 4

Jack Frost----- Amelia Earheart--- George Washington -----Joe Shmoe

Marie Curie----- Abe Lincoln --------Hillary Clinton -------Marc Summers

(The lines are just to hold the place so you can see where everything should
be.)

And then do the next room.

How should I do this? How should I structure my table(s)?

The hard part (I think) is getting multiple columns that each have a
different record.

Thanks!

 

[Otto Moehrbach]

You say: "The hard part (I think) is getting multiple columns that each
have a different record." What do you mean by "record"?
I imagine placing these "charts" in different sheets, one "chart" or room,
per sheet. The sheet would have the room number at the top. The "chart"
would consist of placing the names in individual columns (such columns are
called "rows" in a classroom), with maybe a blank narrow column between the
"rows" to separate them, and maybe blank rows to provide some separation
front to back, very much what you showed in your original post. Does this
sound like what you want? HTH Otto

 

[Bernie Deitrick]

Set up another worksheet with the formatting that you want, with Row numbers across row 2 as the
heading , and Seat numbers down column A starting in row 3 as the labels. Use values that match the
values in your data table - for example, if you have Row 1 as the entry, use Row 1; if you have 1
as the entry, then just use 1.

Put the Room number in another cell, let's say cell A1.

In each of the cells where you want a name, enter the formula

=IF(SUMPRODUCT((DataSheet!$B$1:$B$100=$A$1)*(DataSheet!$C$1:$C$100=B$2)*(DataSheet!$D$1:$D$100=$A3)*ROW($B$1:$B$100))<>0,INDEX(DataSheet!$A:$A,SUMPRODUCT((DataSheet!$B$1:$B$100=$A$1)*(DataSheet!$C$1:$C$100=B$2)*(DataSheet!$D$1:$D$100=$A3)*ROW($B$1:$B$100))),"")

This assumes that names are in column A, rooms in column B, Rows in C, and Seats in D of sheet
DataSheet. Change the 100 to a number at tleast as large as the highest row of your data table

Then copy that formula to all the other cells where you want names to appear.



HTH,
Bernie
MS Excel MVP

 

[jschping]

Wow! That looks delicious! Could you please explain how it works?

Thanks!

 

[jschping]

Hi,

I also got a circular reference error emssage when I copied it to the other
cells. It only worked for A3. What am I doing wrong?

Thanks!

 

[Bernie Deitrick]

A3 must contain the row designation value, not the formula. The formula
could be in cell B3, but you need at least two rows above and one column to
the left of your seating chart area.

Basically, it works by determining the row with the data of interest, and
pulling that name, by comparing all the values in your table with the values
that you want - class, row, and seat.

HTH,
Bernie
MS Excel MVP

 

[Herbert Seidenberg]

Excel 2007 Chart
Label seats.
Real seats.
http://www.mediafire.com/file/z4otqnnj3fn/11_10_09.xlsm

 

[jschping]

Thanks so much! It's beautiful!

Is there a way to make it 6 rows? In my school for finals we have 6 rows
per room.

Thanks!

 

[Herbert Seidenberg]

Excel 2007 PivotChart
Added a row and revised method.
No more formulas, easier to scale.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/11_10_09.xlsm