Seating Chart

Hello,

I am creating a seating chart for a banquet. All the banquet attendees and their information is provided through a registration website. Currently, I assign people to a table using an ID number. Once someone has been sat, column E will be blank when someone is sat and NO TABLE when they are not sat. Can I edit this so instead of blank it will actually say which table they have been sat at? My current set up has this function:

=IF(COUNTIF($I$3:$W$30,'Seating Assignments'!$D2)=1,"","NO TABLE")

Thanks!

Unfortunatly, I have looked at that post many times. This banquet will be 450+ people so a drop down list isn't ideal. My set up allows me to sort by name or company to easily find the people needed and then drop their assigned ID into my table. I just want it to return a table number value so when I send out my mass email with everyone's table assignments I do not have to manually enter anything.

Yes, 2019 currently installed. I prefer to use google sheets but many of the functions were not working and I did not want to spend time translating into a form google could read.

Are you saying that you enter the ID numbers into the cells in columns I to W, and that you want the appropriate heading (Table number) to appear in column E?

Note that although the formula that you quote in your first post refers to cell D2, within the sample file it refers to cell D1 (unless you want to disregard row 2 of columns A to E - but then, why have an ID of 1 in cell D2 ??)

Pete

E2
=IF(COUNTIF($I$3:$W$30,D2)>0,INDEX($1:$1000,SUM(INT(($I$3:$W$30=D2)*ROW($I$3:$W$30)/10))*10+2,SUM(($I$3:$W$30=D2)*COLUMN($I$3:$W$30))),"No Table")

You can use this array* formula in cell E2:

=IF(COUNTIF($I$3:$W$30,'Seating Assignments'!$D2)=1,INDEX($I$1:$W$30,(INT((MIN(IF($I$3:$W$30=$D2,ROW($I$3:$W$30)))-3)/10)*10)+2,MIN(IF($I$3:$W$30=$D2,COLUMN($I:$W)))-8),"NO TABLE")

then copy down.

* Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

You might also think about using conditional formatting on the cells in column E to alert you to the possibility of entering an ID number more than once. You can use a CF rule similar to your quoted formula, i.e.:

=COUNTIF($I$3:$W$30,'Seating Assignments'!$D2)>1

applied to cells E2:E361 and turn the background colour to red.

Hope this helps.

Pete

Thanks for letting me know, John - I didn't check on that, but I just thought it would be a good idea to include it.

Pete