Fixed Column changing Row

Hi Cliff,

There are no macros in Numbers. That's an Excel term. With Numbers you can do similar things to macros using AppleScript.

But AppleScript doesn't seem to apply here.

I'm puzzled by your description. Could you give more specifics on what you are trying to do? Maybe OFFSET or INDIRECT and perhaps ROW will help. But it's hard to say for sure.

Also, I hope you are breaking things up into discrete tables Numbers-style, and taking advantage of Footer Rows etc.. 1000 rows sounds like a lot.

SG

If you insert a row between 8 and 28 or between 29 and 34, etc., the formulas will automatically adjust for the change and the sum value will update accordingly. Is this what you are asking?

MS

I would like to have 6 Bi-Monthly totals & a Grand total at the bottom of the document.

In Numbers an easy way to do this is:

Note you need only one formula. Here it's in B2 of 'Summary' copied down:

=SUMIF(Cashbook::$B,A2,Cashbook::$C)

The last row in each table is defined as a Footer Row.

The sum formula in 'Cashbook' is simply =SUM(C).

The sum formula in 'Summary' is =SUM(B).

In general I would advise against using big tables with lots of blank cells in them. Breaking things down into separate tables can simplify your work and save a lot of time trying to maintain complicated formulas referring to ranges in a big table.

SG

Hi Cliff,

Following on from SG's suggestion of small, discrete tables, this may work.

Enter dates of each payment in column A of Data Entry. Column B will extract the month number from A. Formula in B2 (and Fill Down)

=MONTH(A2)

Column C finds the Bimonthly Period by looking up the month number in the Lookup Period table and returning the Period. Formula in C2 (and Fill Down)

=VLOOKUP(B2,Lookup Period::A:B,2,FALSE)

I have set the periods to start in January. Adjust if you are using financial years starting in July.

Enter payments in Column D. You can hide columns B and C to make a neater data entry table (and less chance of accidentally typing into B or C).

Third table, Summary by Period, uses SUMIF

Formula in B2 (and Fill Down

=SUMIF(Data Entry::C,A2,Data Entry::D)

Each table has a Header Row and a Footer row so the SUM formulas in the footers are as SG said.

After hiding B and C of Data Entry, and moving Lookup Period to another sheet, here is your work area:

Regards,

Ian.

Hi Cliff,

I started speadsheeting in the 1980s with Claris Works. In hindsight, it was Excel-like. Then I was 'forced' to use Excel (my employer decided that we would all use Windoze Excel for compatibility). A good corporate decision. And I obeyed.

When I retired, (Yeehah!) I chose to go back to Mac. At first I was puzzled with Numbers. It took a comment from Barry in this forum for me to understand. Barry said: "Excel provides an "ocean" of a sheet, with several small "islands" of data dotted here and there. Numbers uses several small, discrete tables, each with a purpose." That thought is echoed by SG in this thread.

Happy Numbering!

Regards,

Ian.

Hi Cliff,

Numbers has documents, sheets (tabs), and tables. You can have multiple tables all together on one sheet. A table is an efficient way of organizing data that makes references easier. Tables are also commonly used in Excel these days too. Recommend using them and as much as possible avoiding all those blank rows and columns.

SG

Hi Cliff,

Yes, you can have everything on one sheet. However, I have never had a problem with linking formulas between cells, tables or sheets. As you move objects around, the links (cell references) automatically adjust.

One problem you may find with having everything on one sheet is that Numbers does not have "Set Print Area".

Numbers is designed to display a "presentation" (summary) on its own sheet, whilst hiding the Database and the Number Cruncher on their own sheets. Then print (or display on screen) only the sheet with summarised results. That will save you many headaches.

Numbers does get slow if a document includes large tables. If you don't need to constantly refer to the whole table, consider summarising "old" data in a small table. For example, data from last year can be summarised. Archive the raw data elsewhere. Tab-delimited text files will hold archived data that (I believe) will still be accessible in years to come.

Regards,

Ian.