Wedding Planner

[McKnze21]

Hi All,

Hope all are well!!

Looking for a little assistance with an Excel spreadsheet I am creating for a friend to organise her wedding. I have a tab for a seating plan from the guest list which is on another tab and I'd like if possible for the names to automatically be placed in some sort of table layout??

Also, I have a column on the guest list tab next to the names which I want to show the table & seat number. Is there a formal or way of having Excel search the seating plan and then for the table and seat that the name corresponds to, then enter that result in to the column on the Guest tab?

I have attached spreadsheet for you all to see. Any advice on how to make this so much better is welcomed!!

THanks!!

 

[Fluff]

Ok, slight change to the formulae on the day tab.
In C2

=IF(A2="","",IFERROR(INDEX('Seating Plan'!$N$4:$Z$4,AGGREGATE(15,6,(COLUMN('Seating Plan'!$N$4:$Z$4)-COLUMN('Seating Plan'!$N$4)+1)/('Seating Plan'!$N$5:$Z$16=A2),1)),""))

And in D2

=IF(A2="","",IFERROR(INDEX('Seating Plan'!$M$5:$M$16,AGGREGATE(15,6,(ROW('Seating Plan'!$M$5:$M$16)-ROW('Seating Plan'!$M$5)+1)/('Seating Plan'!$N$5:$Z$16=A2),1)),""))

Then in N2 filled down put

=IFERROR(INDEX($A$2:$A$156,AGGREGATE(15,6,(ROW($A$2:$A$156)-ROW($A$2)+1)/($C$2:$C$156="")/($A$2:$A$156<>""),ROWS(N$2:N2))),"")

Then on the formula tab select Name Manager, New In the Name box put Guests & in the Refers to box put

=OFFSET(Day!$N$2,0,0,LOOKUP(2,1/(Day!$N$2:$N$200<>""),ROW(Day!$N$2:$N$200)-ROW(Day!$N$2)+1))

Finally, change the data validation source from

=Day!$A$2:$A$156

to

=Guests

this will remove the names from the list once they have been allocated a table.

 

[McKnze21]

What is the need for the N2 Forumlae? Just out of curiosity?

It seems to all work well apart from the N2 fill down starts from A8 so it misses out the first 6 names? Any idea for this?

Amazing help by the way!! Very much appreciated!!

 

[Fluff]

The formula in N2 on the Day sheet shows all guests who have not been allocated a seat, so the dropdowns on the Seating Plan sheet only shows the names of the guests who have not been given a seat.