How can I enter data on one sheet, and have it auto-populate into another?

Hi ziggyonice,

It is much harder to consolidate data from 12 different sheets than it is to extract data from a single data entry table to different report tables.

So in your situation I would start with the single table where everything gets logged and have a report table that gathers each month as you envision your sheet 13. Drawing from a single table will open up many possibilities for examining your data. With your monthly tables it could be a mini battle each time you think of the next piece you would like to see.

Does this seem like a change you would be willing to make?

quinn

HI Ziggy,

Here's an example, based on your data for Month 1. I've named the tables Month 1, Month 2, etc., but it should work just as well with the tables named January, February, etc., provided that the Table names match the labels for columns B through M of the Summary table.

10 months 'data' was entered in the monthly tables. All data numbers are the same as Month 1 on your sample, except for the "calls" row, where data was varied to check the Average function in the final column of Summary. As written, a null string is placed in each cell of Summary corresponding to an unfilled cell in the monthly tables. A null string is a text string of zero length, and, as a text value, is ignored by AVERAGE.

Monthly tables contain only entered values.

Summary contains two formulas:

B2, and filled down to B5 and right to column M: =IF(LEN(INDIRECT(B$1&"::B"&ROW()))>0,INDIRECT(B$1&"::B"&ROW()),"")

Both INDIRECT statements are the same. The first is used by IF to test for an entry in the target cell by measuring its LENgth. If the LENgth is greater than zero characters, then the entry is copied into the cell containing the formula. Otherwise (ie. if there's no entry), a null string ( "" ) is placed in the cell containing the formula.

The arguments for INDIRECT determine the address of the cell from which INDIRECT is to get the value. B$1 returns the Table name from cell B1. &"…" adds the text between the quotation marks. &ROW() adds the number of the row containing the formula. For the instance of the formula in Summary::B2, the constructed cell address is "Month 1::B2". and the value in that cell is returned to Summary::B2.

N2, and filled down to N5: =AVERAGE(B2:M2)

This calculates the Average (mean) of the values in columns B to M of the row containing the formula. 'Empty" cells (which actually contain a null string) are ignored.

Regards,

Barry

I tend to agree with quinn. It's generally *much* easier and more efficient to do data entry in one table, and extract reports from that, than it is to enter data in multiple tables (particularly if they're all on different sheets, which is going to result in particularly long, confusing formulas) and then consolidate the data from these tables.

If you have a look at the templates provided with Numbers 3 (File > New in your menu) you'll note good examples (the 'Personal Budget' is one) of the easier, but very powerful, approach.

Applying that approach here, something like this can be done (to save space, I've gone through March, but it's easily extended through December):

No formulas in the data table. Just one formula in the months columns of the Summary table, cell B2, copied right to column D and down:

=SUMIFS(Data::$C,Data::$B,$A2,Data::$A,B$1)

The formula to calculate average, in E2 copied down the column:

=AVERAGE(B2:D2)

This formula will become =AVERAGE(B2:M2) after adding columns for the remaining months.

With this kind of a setup it's also easy to add to the analysis: highest, lowest, variance from target, etc.

SG

Having a summary table auto-populate based on data in other tables is doable, as Barry has demonstrated, but it's a bit messy and complicated.

Curious why your employer might be opposed to collecting data in a best-practices standard "database" columns format that's been in use for decades!

(If it's because you need nice-looking monthly reports as you go along, then those are easily extracted from the data table and can look virtually indistinguishable from your original individual data entry tables).

The principle of separating data entry from summary calculations, where possible, is also best practices.

As a practical matter, going with what the boss wants, and then, when it comes time to pull together summaries, just copy-pasting the values into one data table, adding a column for the month number, and having the summary table auto-populate from that should work well.

SG

Hi ziggyonice,

Best practices are just that. Nothing is ever prefect. I had an overly complicated spread sheet that served me for years until I finally got fed up and streamlined it this year. I remind myself that there are three ways so to anything- the right way, the wrong way and the way we do it.

Barry's solution for you is really not that complex I think it will serve you here. At its heart it is a very simple formula that is filled thoughout your table.

I have stripped away the IF/LEN error trapping to make it easier to understand. That will throw off your average because a blank cell won't be averaged while a "0" will be.

His formula says create an address using B1 as the table name, "B" as the column and the row that the function is in for the row. So in B2 in the summary table this looks for the value in B2 of the table "Month 1". When your tables have unique names we don't have to specify a sheet name.

Hope this makes it look easy.

quinn

Hi Ziggy,

"If I want to have the annual sheet auto-populate based on the values from entered each month, how does that change the equation, as the tables are each on their own separate sheets?"

No changes needed at all, provided each table has a name that is unique within the document.

Regards,

Barry

ziggyonice wrote:

Thanks again for the very helpful information.

Just one final question: how would I calculate the average of a row across several columns?

Per my example above, the simplest way is:

=AVERAGE(B2:D2)

Or, more likely since you have more columns,

=AVERAGE(B2:M2)

... where the values you are averaging are in columns B through M.

And, if you use the "database format" approach you can simply use AVERAGEIFS without having to set up the rows to average. Just pull the average right out of the source table.

Here is another consideration to keep in mind when using a complicated function like INDIRECT. It can be very useful in some situations. But it is what is known as a "volatile" function that recalculates every time you do anything, anywhere in your document. You won't notice the difference with small documents, but this volatility can lead to sluggish performance in larger ones. Numbers isn't a speed demon to begin with, so if crisp performance is desired, then generally it's not a bad idea to avoid INDIRECT. In most situations (I would include this one) there are ways to accomplish the same objective with more clarity and speed.... And without having to worry about making sure all those "hard-coded" table names and column headers match up exactly.

SG

HI Ziggy,

"Just one final question: how would I calculate the average of a row across several columns?"

See the Summary table in my earlier post. The monthly numbers are pulled into columns B through M. The average is calculated in column N, using this formula:

N2, and filled down to N5: =AVERAGE(B2:M2)

Regards,

Barry

Hi SG,

Thanks for the tip about INDIRECT(). You know I am all about maximizing performance on minimal hardware.

quinn