How to populate an invoice table from the bottom up with only services having associated costs from another Numbers table?

It is possible to do, and you don't need AppleScript - although I agree the 'bottom-up' approach is odd, but it's not my invoice :)

The solution (at least the one I came up with) requires adding two additional columns to your table.

Instead of:

Add a new column for the index order, and a new column for the displayed name, like:

These cells can be hidden after setup, but for now leave them visible.

The formula in cell A2 is:

=IF(B2>0,ROW()−1,0)

This triggers the cell so that it shows the row number (-1 for consistency) when the value in B2 is not zero - if no cost => index value is 0.

Fill this formula down and you'll see the numbers populate for any cell that has a corresponding value.

Now, for cell C2, we do something similar - if the cost is >0 then we copy the SERVICE value:

=IF(B2>0,D2,"")

Now the table populates the 'Displayed' column for any row that has a cost associated with it.

Now we're almost done.

For the magic, we simply grab the array of Cost and Displayed cells from this table and sort it by the index column. Cells that have no value will sort to the top:

Create a new table (or edit the invoice table itself), and set the first cell of the service list to:

=SORTBY(Line Items::B:C,Line Items::A)

This grabs the cells from columns B:C in the 'Line Items' table (or whatever you called it), and sorts it by the values in column A of that same table.

The rows from the source table that have no cost will be sorted first (because their index is 0), and their corresponding Displayed value is also blank:

The last thing to do is format the Cost column so that $0 items are suppressed via a Custom Format

and Viola!

As Camelot showed, while it is possible to fill the table from the bottom, it makes a simple job difficult. Filling from the top would be a simple FILTER function in one cell that "spills" into other rows as needed.

Instead of a bunch of blank rows at the top of the invoice, how about hiding the blank rows using a filter? In the screenshot below, the filter is turned off. If it is on, rows 4-7 will be hidden.

Formula in cell A2 =FILTER(Table 1::A:B,Table 1::B≠0)

The table has three footer rows at the bottom for the calculations.

The other thing from your original tables that adds to the complexity is the columns being swapped. Service and Cost are columns B and A, respectively, in the first table but are A and B in the second. I changed their order in the first table.

One thing not addressed in this example is what you meant by "ignoring any blanks, duplicates, or zero cost items". The formula will filter out blanks and zero cost items but not duplicates (like if you have two or more rows in the first table that are labeled "ceiling" and both have a cost next to them).

And here is an alternative idea. What I show below is pretty basic but it gives the general idea. The advantage of this one is that you can use Table 1 to enter your costs individually (on separate rows) as they are incurred so you can refer back to what you spent on what (and possibly see if you missed entering something). For instance, you might have ten costs associated with "ceiling" and each will have its own row.

Column A of Table 1 is a column of popup menus of the five services (plus a blank). Click on a popup menu to choose a service then enter a cost and add some notes.

Column A of Table 2 is a list of the five services, typed in.

Column B of Table 2 sums up the costs for each of them.

A filter on Table 2 will hide all rows that are 0. The filter is shown but is not currently on.

Formula in Table 2::B2 =SUMIF(Table 1::A,A2,Table 1::B)

Fill down to row 6 with that formula

Last three rows of Table 2 are footer rows, same as before.

The "from the bottom up" is the hard part. It seems a bit unnatural when considering how spreadsheets generally work.

Is there a way you can rearrange your work so that you can avoid that?

SG