Previous 1 2 Next 19 Replies Latest reply: Aug 3, 2014 1:48 AM by Cliff Peters
Cliff Peters Level 2 Level 2 (190 points)

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)
  • SGIII Level 5 Level 5 (5,275 points)

    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

  • Cliff Peters Level 2 Level 2 (190 points)

    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

     

     

          Screen shot 2014-07-20 at 6.30.12 PM.png

     

    Screen shot 2014-07-20 at 6.31.57 PM.png

  • Listen Inn Level 1 Level 1 (85 points)

    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

  • SGIII Level 5 Level 5 (5,275 points)

    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:

     

    Screen Shot 2014-07-20 at 10.24.42 AM.png

     

    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

  • Cliff Peters Level 2 Level 2 (190 points)

    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

  • Cliff Peters Level 2 Level 2 (190 points)

    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

  • Yellowbox Level 5 Level 5 (6,225 points)

    Hi Cliff,

     

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

     

    Screen Shot 2014-07-21 at 5.50.32 pm.png

     

    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

     

    Screen Shot 2014-07-21 at 5.59.29 pm.png

     

    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:

     

    Screen Shot 2014-07-21 at 6.08.48 pm.png

     

    Regards,

    Ian.

  • Cliff Peters Level 2 Level 2 (190 points)

    Hi SG

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

     

    Regards Cliff

  • Cliff Peters Level 2 Level 2 (190 points)

    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

  • Yellowbox Level 5 Level 5 (6,225 points)

    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.

  • Cliff Peters Level 2 Level 2 (190 points)

    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

  • SGIII Level 5 Level 5 (5,275 points)

    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

  • Yellowbox Level 5 Level 5 (6,225 points)

    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.

  • Cliff Peters Level 2 Level 2 (190 points)

    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 Next