Previous 1 2 19 Replies Latest reply: Aug 3, 2014 1:48 AM by Cliff Peters
Level 2
iPhone

Hi,

I'm fairly sure there is no formula for what I would like to achieve ??

Is there a Macro? that can be used.??

I have a spreadsheet that I would like to be able to recognise a variable cell reference.

I.E. the formulas will look similar to this when complete.

Cell "F1000" =SUM(F8:F28), Cell "G1000" = SUM(G29:G34)...

Cell "F1001" = SUM(F29:34)...

Cell "F1002" = SUM(F35:64)...

etc

The Row numbers will change until I complete that portion of the spreadsheet.

I have found a way to display the final row numbers as they correspond to a date.

A way I can do this manually is to type the cell reference & formula then Fill Right.

I have never had any joy creating Macro's & don't understand the language or how they work.

Cheers for any Ideas

Cliff

iMac, Mac OS X (10.6.8)
Solved by SGIII on Jul 20, 2014 7:34 AM Solved

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

• Level 6
Mac OS X

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

• Level 2
iPhone

Hi SG and others,

I meant to say AppleScript.

OFFSET, INDIRECT & ROW don't seem to be appropriate.

Below Row J is for the date entry, Row 1154 tells me when each BiMonth starts & Ends. Columns N to BV contain amounts that I would like to have 6 Bi-Monthly totals & a Grand total at the bottom of the document.

On the screenshot I have put all dates on one page but in reality these will be spread over rows 12 to 1151.

Hopefully the pictures help with my description.

AppleWorks handles it still on Snow Leopard.

Is this too much for Numbers? Is smaller Tables less taxing on Numbers? I think I would probably still need the same amount formulas & Data.

Thanks for any assistance.

Cliff

• Level 1

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

• Level 6
Mac OS X

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

• Level 2
iPhone

Hi MS,

Thanks for your advice, I'm not sure it will help though. I will look closer at what you have said & see if I can understand it better.

Cheers Cliff

• Level 2
iPhone

Hi SG,

This looks promising, I created a new table for the first time, entered SUMIF & clicked on the relative row & the row Info was displayed. I think I can adapt this by changing some existing formula's to display month numbers & periods. I will also see if this can be done automatically depending on the Table header??

I think I may try to reduce some of the formula's in my monster SS.

Regards

Cliff

• Level 6
Mac OS X

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.

• Level 2
iPhone

Hi SG

Very Cool Formula, this is very helpful & will eliminate a number of formulas.

Regards Cliff

• Level 2
iPhone

Hi Ian,

The further explanation is great I was wondering how the periods where achieved. I very much appreciate the way you explain things. I'm looking forward to exploring Numbers more now.

Regards

Cliff

• Level 6
Mac OS X

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.

• Level 2
iPhone

Hi Ian

I kind of like the ocean, All the info on one SS, I learned not to trust inter sheet references when I was using Appleworks. When I create my next SS document I will have a go.

Is it better to have separate SS's or dose tables within one SS also work. I've noticed Numbers slows a bit when the SS gets too big & I had an unexpected Quit once.

Cheers

Cliff

• Level 6
Mac OS X

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

• Level 6
Mac OS X

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.

• Level 2
iPhone

Hi Ian,

My ocean has burst it's Sea Wall. Became painfully slow at hiding columns & rows.

Looks like I'll need to start again.

I think I can use SUMIF or SUMIFS to achieve what I am trying to do. Thanks SG also for this fab formula.

Should be able to remove 58 Rows if my cunning plan works!!

I'll be back when I check.

Regards

Cliff

Previous 1 2