Dynamic table size (number of rows)

Question

Level 1

31 points

Dynamic table size (number of rows)

Hello,

I have a spreadsheet with a number of items (in this case, to-do items); let's call it the "base table". I want to create a "view" or report based on this base table. So for example, I'd like to have a second table that shows only the high-priority items in the base table.

Solutions that don't quite work:

Filter the base table to show only high priority items. Problem: I want to have both the base table and a separate filtered view.
Create a "view table" which directly copies over the rows of the base table; then, filter the view table. Problem: As the base table grows, I would have to manually increase the length (number of rows) of the view table to be at least as long.
Play complicated tricks to extract just the relevant rows, as in: https://discussions.apple.com/thread/2804418?answerId=13329113022#13329113022 Problem: Somewhat annoying, which I can live with; but also, it again requires manually resizing the table as the number of rows in the view grows. And I'm wondering

Solution 2 seems closest to what I want. Is there a way to fix this to solve the dynamic table size problem? That is, something like:

A way to create a table V which is a copy of table B, where changes to B show up inside V including changes in the number of rows.
A way to fill in a variable number of rows in a table, expanding the table if necessary

Even better, although this is probably too much, is if there were a way to create multiple views of the base table, such that any one of the views could be edited, and would be reflected in the base table.

This is on the latest version of Numbers (v. 3.2, 2014) on OS X v. 10.9.2.

Posted on May 27, 2014 11:05 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

May 27, 2014 12:54 PM in response to pbg

If you like the idea of the "view table" then you set that up and then whenever you want to filter you just select the cells in table B, command-c to copy, click in the upper-left cell of the "view table" where you want the values to appear, and command-v to paste. The "view table" will expand automatically to accommodate the number of rows you paste from table B.

There are more complicated ways using formulas or AppleScript, but the simple approach sounds as if it might work best for you.

SG

Reply

Answer 2

pbg Author

Level 1

31 points

May 27, 2014 2:06 PM in response to SGIII

Thanks. But I'd rather not have to copy-paste every time; I want it to appear automatically. Are you suggesting there's a way to do this with formulas?

Reply

Answer 3

SGIII

Level 8

37,532 points

May 27, 2014 2:16 PM in response to pbg

Thanks. But I'd rather not have to copy-paste every time; I want it to appear automatically. Are you suggesting there's a way to do this with formulas?

Yes, there is a way to do this with formulas and extra index columns and things, or with AppleScript. I think you linked to a thread that describes the formula approach, right? Perhaps others can post an update of that type of solution. I'm not a big fan of the formula approach when it gets complicated and the results can't be sorted. Plus, as you point out, one has to worry about about table dimensions.

A few clicks and keystrokes and a filter and I've got what I want within seconds via the copy-paste approach.

SG

Reply

Answer 4

May 28, 2014 8:59 AM in response to pbg

I think you have a vision of Numbers as a very powerful program that can be customized to do things just the way you want, and with little effort on your part. Meanwhile, Apple is going the direction of making a program that does just the basics, in a rather limited way that serves the needs of the beginner spreadsheet user who hasn't yet developed sophisticated tastes in layout.

Compromise is the word of the day here.

It's very easy to turn Filtering on and off. Just a click of the checkbox in the filter pane is all it takes to go from the full display to your filtered view.

Jerry

Reply

Answer 5

SGIII

Level 8

37,532 points

May 28, 2014 9:53 AM in response to pbg

Not sure I agree with the comment that Numbers is not (or is no longer) a very powerful program. It is. In a number of ways it's more powerful than before. It has recently added some advanced functions for formulas. And it now supports AppleScript markedly better than before. Plus it has added powerful interactive charts that even work well cross platform. These are not "just the basics" types of features for the beginner spreadsheet user.

Numbers is not, however, a desktop publishing program for fancy printed layouts. Nor is it a dedicated database front-end. The kinds of things you are asking it to do are possible. But they are just not that easy or efficient–whether you're working in the new Numbers, in the old Numbers, or in Excel.

For the specific problem stated in this thread, taking advantage of the built-in filtering functionality coupled with good old copy-paste is more efficient, at least for me.

SG

Reply

Answer 6

pbg Author

Level 1

31 points

May 28, 2014 10:49 PM in response to SGIII

Thanks to both of you for the replies.

I think this wouldn't be such a far-out feature. For example, some of Numbers' functions return arrays of cells; if it dumped this array into a corresponding array of cells, like Google Docs does, that would solve the problem. (Instead you can only pick out one element of an array and put it in a cell.)

I guess I would have to investigate AppleScript if I really want to make this happen. A full featured programming language is indeed the answer in general... although I wish it were something more common like Python.

Reply

Answer 7

SGIII

Level 8

37,532 points

May 29, 2014 9:47 AM in response to pbg

An AppleScript that takes values and inserts them in a different table whose dimensions don't have to be specified in advance (the table will expand automatically) is shown in this thread. The basic idea is to place the array on the clipboard as tab-separated values and then have a script paste them wherever you want in a table.

If you like to work in Python, you can do that and then wrap the Python script in an AppleScript. Hiroto has many examples in the discussions where he does that with Perl.

SG

Reply