Banquet Spreadsheet

[thompsa3]

I'm working with a veteran's group to set up an easy-to-use spreadsheet for them to track guests, table assignment, and meal selection for their annual banquet. The maximum number of guests is expected to be about 600 (10 per table and 60 tables). The meal selection choices are beef, chicken, fish, and vegetarian. Here's a snapshot of the first worksheet, called "Seating".
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
<o><TABLE style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=457 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 3648" width=114><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3232" width=101><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 2944" width=92><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 4800" width=150><TBODY><TR style="HEIGHT: 21pt; mso-height-source: userset" height=28><TD class=xl26 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 21pt; BACKGROUND-COLOR: transparent" width=114 height=28>Last Name</TD><TD class=xl26 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray; WIDTH: 76pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=101>First Name</TD><TD class=xl26 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray; WIDTH: 69pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=92>Table</TD><TD class=xl26 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray; WIDTH: 113pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=150>Dinner Selection</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Brown</TD><TD class=xl25 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Ken</TD><TD class=xl27 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Beef</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Brown</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Alisson</TD><TD class=xl28 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Chicken</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Davis</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Bob</TD><TD class=xl28 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Fish</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Davis</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Sharon</TD><TD class=xl28 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Beef</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Baker</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Trish</TD><TD class=xl28 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Vegetarian</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Baker</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="Al ">Al </TD><TD class=xl28 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Beef</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Jones</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Sam</TD><TD class=xl28 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent">Beef</TD></TR></TBODY></TABLE>

In order to make service as easy and efficient as possible, the hotel has requested we provide a list of meal selection by table. [So the wait staff knows that Table X has Y number of beef meals, Z number of chicken meals, etc.] So, I’m trying to figure out the right formula to do this.
</o>
I have a second worksheet I want to use to create the report for the hotel. I want to pull the Table (Column C) and Dinner Selection (Column D) data from the Seating worksheet to display something like this

<TABLE style="WIDTH: 304pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=404 border=0 x:str><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3232" span=4 width=101><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=101 height=21>Table 1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101>Table 9</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Beef</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 4</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Beef</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 6</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Chicken</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chicken</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 3</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Fish</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Fish</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 0</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" height=23>Vegetarian</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 2</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Vegetarian</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Table 2</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Table 10</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" height=23>Beef</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 7</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Beef</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Chicken</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Chicken</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 5</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Fish</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Fish</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Vegetarian</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 0</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Vegetarian</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 0</TD></TR></TBODY></TABLE>

After searching on the Internet a bit, and trying to use the Excel help feature, the nearest I've come to solving this is the SUMIF formula, which doesn't work.
<o> </o>
=SUM(IF(Seating!C2:C601="1", IF(Seating!D2:D601="Beef")))

I know this would work easy in Access or another database program, that's just not something the veterans have or would be able to use very well. Any ideas?

Thanks.

 

[lenze]

Hi and welcome to the board!!!
Are you familiar with Pivot Tables? Select a cell in your table and choose
Data>Pivot Table Report. Follow through the steps on each panel of the wizard. On the 3rd panel, select a location (New sheet). Click Finish.
When the gird appears, drag "Table" to the Row Field(Far left of grid) and drag "Dinner Selection" to the Data Field (Big area lower right.)
You're done. Not the layout you envisioned, but certainly usable and very simple.

lenze