A seating chart that fills itself: TEXTJOIN, SMALL+ROW tie-breakers and date-relative checklists in a spreadsheet

I built a wedding planner spreadsheet this week (Excel + Google Sheets, no macros). Three formula tricks did most of the work, and they are useful in any tracker, not just weddings.

1. A seating chart that fills itself: TEXTJOIN over a filter

The guest list has one row per household with a Table column. The Seating tab has one row per table. Each table needs to show who is sitting there, as a comma list.

=TEXTJOIN(", ", TRUE, IF((Guests!$H$6:$H$405=B6)*(Guests!$F$6:$F$405<>"No"), Guests!$B$6:$B$405, ""))

• IF(condition, names, "") builds an array that holds the name where the table matches and the guest has not declined, and an empty string everywhere else.
• TEXTJOIN with TRUE as the second argument skips the empty strings, so you get Lily Murphy & Lily, The Patel family, Aiden Carter & Ruby.
• Multiplying two comparisons is an AND that works inside array formulas.

In older Excel this has to be entered as an array formula (Ctrl+Shift+Enter). In Microsoft 365 and Google Sheets it just works. Seated count is the boring half:

=SUMIFS(Guests!$E$6:$E$405, Guests!$H$6:$H$405, B6, Guests!$F$6:$F$405, "<>No")

E is party size, so a "& guest" row counts as 2. Open seats is Seats - Seated, with conditional formatting turning negatives red. Moving a family is now "change one number", and the chart updates.

2. "Next 7 payments" without sorting anything

The Expenses tab has a balance and a due date per row. The dashboard should list the next unpaid balances in date order. Sorting the source table is not an option (people type into it), so the trick is a hidden sort key:

N6: =IF(AND(B6<>"", N(I6)>0, H6<>""), H6 + ROW()/100000, "")

A date is just a whole number in a spreadsheet. Adding ROW()/100000 makes every key unique even when two payments share a due date, which matters because the next step looks keys up by value. Then on the dashboard, row k:

Item:    =IFERROR(INDEX(Expenses!$B$6:$B$255, MATCH(SMALL(Expenses!$N$6:$N$255, k), Expenses!$N$6:$N$255, 0)), "")
Due:     =IFERROR(INT(SMALL(Expenses!$N$6:$N$255, k)), "")

SMALL(range, k) returns the k-th smallest key and ignores the "" cells. INT strips the tie-breaker back off to give the real date. Paid rows drop out on their own because their key goes blank. The same pattern drives "Next 7 tasks" from the checklist, keyed on due date for rows not ticked done.

3. A checklist that moves when the date moves

Every task stores days before the wedding, not a date:

Due:    =IF(B6="", "", Setup!$C$8 - D6)
Status: =IF(B6="","", IF(G6<>"","Done", IF(E6<TODAY(),"Overdue", IF(E6-TODAY()<=30,"Next 30 days","Later"))))

Push the wedding back three months and all 60 due dates follow. Negative numbers work for after-the-wedding tasks (thank-you notes at -14, name change at -60). A phase label like "9-6 months" is a nested IF on the same number, so the list reads like the timelines in wedding magazines, but it is tied to your date.

Small things that made it nicer

• Inputs are yellow, everything else is protected with an empty password. Nobody overwrites a formula by accident, and anyone who wants to change the layout can still unprotect it.
• Budget split from shares: categories carry a share (venue 18%, catering 26%...), target = share x total. Committed uses the actual price if filled and the estimate otherwise: =IF(F6<>"", F6, E6).
• "Room left if all pending say yes" = guest limit - attending - pending. It goes red before you have a capacity problem, not after.
• The whole file is generated from a Python script with xlsxwriter, then recalculated and rendered headless with LibreOffice to check every tab. Regenerating after a change takes a second.

If you want the finished file, it is here: Wedding Planner spreadsheet ($9, also on Gumroad). The formulas above are free to copy into your own sheets.