Numbers tables not shifting downward automatically

Maybe I'm misunderstanding what you're trying to do, or maybe you're misunderstanding Numbers' 'Lock' function...

In general, locked tables don't grow.

Locking a table prevents a user from entering values in the table (although the table may contain references that dynamically pull values from other tables). it also prevents rows or columns from being added.

The main purpose of locked tables is to prevent inadvertent changes to the data (such as by selecting a cell and accidentally typing a new value).

Given the above, I don't understand how:

"... some of these 'locked in place' tables can grow in row-length based on the conditional options made on another sheet"

I don't see how that can be.

If the 'locked' table is pulling data from another table, then it has to be big enough to contain all the results you want. If it isn't, Numbers will throw an error - it won't expand the table, whether or not the table is locked.

On that basis, I don't understand how you're doing what you say, even if the table was unlocked.

Can you shed some more light?

I'm guessing you have filters built into the locked tables or some similar arrangement.

You mention you have a "complicated, multi sheet, multi-table" document and a "customer-facing page." May I suggest that Numbers may not be the best tool for what you are trying to do?

Numbers is great for personal and small business projects. It allows elegant solutions, and simple collaboration.

But its design generally is not suited to complicated structures for multiple users.

SG

Hi FleursDuMois,

Thank you for joining this user-to-user help forum. Welcome!

FleursDuMois wrote:

Snip ✄

These customer-facing pages have multiple tables

End Snip ✄

Umm... please explain "customer-facing pages".

...many locked in place for page layout reasons.

I do not understand "page layout reasons". Perhaps you are asking for advice on how to arrange tables on several sheets in Numbers.

And some of these 'locked in place' tables can grow in row-length based on the conditional options made on another sheet.

No, as Camelot has already explained: locked tables are locked. They do not expand.

Please examine the excellent Numbers Templates that demonstrate the Numbers approach:

• Keep the input tables on their own sheet;
• If necessary, keep the boring calculations on their own sheet;
• Use a Presentation sheet to show your table of summaries and perhaps some graphics to engage your audience.

Happy Numbering!

Ian.

OK, the fact you're using filters to control what's displayed makes a lot more sense, although I don't think it's going to solve your conundrum...

The issue is not with locked vs. unlocked tables. It's with filters.

Yes, unlocked tables will shift around on the page to accommodate new rows added to/deleted from nearby tables. This is true for your summary/'client-facing' tables, too.

The issue you're facing is that when you filter a table, this does not trigger the same table dance on the page. Technically you're neither adding rows to, nor removing rows from the table, so other tables see no need to shuffle. This is true whether the table in question is locked or not.

So your main table has to be big enough to accommodate the maximum number of rows you could want to display. The fact you then decide (via filtering) to hide some of those is of no relevance to the other tables.

Off hand, I don't see an easy way of achieving what you're trying to do... it would likely involve shifting the filters to formulas so that you can calculate how many rows you need, but it's an uplift, for sure.

I think it's not unreasonable to expect that using filters to reduce the number of displayed rows in a table would have a impact on other tables's positions, just as if rows had been added or deleted. That seems like a valid feature request to me.

The other approach I thought about may depend on the nature of the tables you're building... If it's just a Totals table at the bottom you could put those into Footer Rows - these are rows that are displayed at the bottom of a table, would automatically scale based on the displayed rows, and wouldn't be affected by the filter.

Here's an example I threw together:

The 'Back-End' table is pretty straightforward... a list of all the options and their prices. A simple calculation in the Total field works out the price if the corresponding Qty cell is > 0

The Customer table has four critical elements attached:

1) The full table has to have at least as many rows as the Back End table (or, more specifically, at least as many rows as you'd ever likely want to show).

2) One Footer row (Via Inspector -> Table -> Headers and Footer. Here I have it set to 1, so the bottom row is fixed.

3) The formula in cell A2 is:

=FILTER('Back-End'::A:E,'Back-End'::D>0,"")

This performs the first-level filter - it extracts all the data from Back-End::A:E where the value in column D is > 0 (i.e. there's a quantity set).

This has the effect of pulling all the non-0 quantity lines into this table (with a chunk of blank lines afterwards, like:

Which brings us to...

4) A filter is applied to this table to filter out any rows that are blank:

So now the empty rows in the table are suppressed. However, you still have a Totals row since that is part of the footer. In this row (or rows - you can designate up to 5 footer rows) you can put your blurb, Totals, signature lines, etc.

The filter is dynamic - as soon as you set a quantity on any other row in the Back-End table, it will automatically appear in the Customer Table (and disappear if the Qty is set to 0 or blank).

Here you can see I've set a Qty of 4 for item 'r', and it's added to the table, as well as expanded the footer to 5 rows with a signature/approval line:

(since the table is highlighted, you can also see the suppressed rows 9 through 40 since they're blank and filtered out).

This doesn't help if you want more than 5 rows at the footer, but hopefully this does get closer to what you're trying to achieve..?