Steps
- Open the
export-tab-template.csvfile below in Google Sheets or Excel. This is the shape your export tab should end up with, not a finished product. - Look at
messy-working-tab-example.csvto see the kind of "before" tab this is built to sit next to. Your own working tab can keep its own messy layout. Don't touch it yet. - Rebuild the export tab's columns in your own sheet, using the column list below.
- Use the formula pattern below to make each export row pull its values from your working tab automatically, so the export tab fills itself in.
- Before you connect anything else to this sheet, run the data plumbing checklist on it.
- Use the AI and rules card to decide which parts of your invoicing process are safe to hand to AI, and which never are.
The export tab, column by column
record_id: a fixed ID that never changes, even if the row moves or gets sorted.
client_id: your own internal client code. Never the client's name, in case two clients share a name.
client_name: the client's name, spelled the same way every time.
month: the billing month this line belongs to, written as YYYY-MM.
line_item: what this line is for, in plain words.
category: a short label, like "service fee" or "passthrough."
amount: one number, no currency symbol in the cell.
currency: the currency code, like USD.
status: draft, approved, or sent.
locked: Y once the month is closed, N while it is still open.
last_updated: the date this row last changed.The formula pattern
Paste this into your sheet once your messy working tab and your new export tab both exist. It assumes your working tab is named "Working" and its columns run from A to F, with a client ID in column A. Change the column letters to match your own layout.
In your export tab, row 2, one formula per column:
client_id =IFERROR(VLOOKUP($A2, Working!$A:$F, 1, FALSE), "")
client_name =IFERROR(VLOOKUP($A2, Working!$A:$F, 2, FALSE), "")
line_item =IFERROR(VLOOKUP($A2, Working!$A:$F, 3, FALSE), "")
amount =IFERROR(VLOOKUP($A2, Working!$A:$F, 4, FALSE), "")
locked =IF(D2<>"", IF(TODAY()>DATE(YEAR(D2),MONTH(D2)+1,5), "Y", "N"), "")
Fill row 2 down as far as your working tab has rows. Once a row's month locks, copy that row's formulas and paste them back as values, so a later change to the working tab can never quietly change a locked row.The data plumbing checklist
Run this on every workbook before you build anything on top of it. These four rules are what make a clean export tab stay clean.
1. One naming rule. The same service or line item gets the exact same name everywhere, in every tab, every month.
2. A lock on every closed month. Once a month is done, nobody edits that row again. Ever.
3. Every change becomes a new record. Nothing gets overwritten, so there is always a full history if someone asks what changed and when.
4. Every record has a fixed ID. The ID never changes, even if the row moves or the layout around it changes.The AI and rules card
Decide this before you let anything automated touch your invoicing. Print it, or keep it next to your build notes.
AI extracts and drafts. Rules decide.
Give to AI: reading a messy document or PDF, pulling the numbers out of it, drafting a first version of an invoice or a line item.
Never give to AI: any math, any routing (deciding which client or account a line belongs to), or unlocking and editing a closed month.
Before anything touches money: a person reviews the draft and signs off. Nothing goes into your books, and nothing goes out to a client, without that approval.