11 Replies Latest reply: Mar 14, 2009 7:18 PM by Ric Donato
mjm394 Level 1 Level 1 (0 points)
Is there a way to fill down a formula or function in a empty column without dragging the fill handle through ~2500 rows in a worksheet?

iMac, Mac OS X (10.5.6)
  • 1. Re: autofill without dragging fill handle?
    Barry Level 7 Level 7 (29,180 points)
    If your table has a header row, entering the formula into the header row will automatically enter it into every cell in the column (except the Footer cell at the bottom, if you have one.

    Regards,
    Barry
  • 2. Re: autofill without dragging fill handle?
    mjm394 Level 1 Level 1 (0 points)
    Thanks
  • 3. Re: autofill without dragging fill handle?
    Level 8 Level 8 (41,760 points)
    CAUTION

    I really like this feature but it was dropped during the switch to iWork '09

    Yvan KOENIG (from FRANCE mercredi 21 janvier 2009 11:57:25)
  • 4. Re: autofill without dragging fill handle?
    eyeless Level 1 Level 1 (35 points)
    If there is a way to automatically renumber a numbered column I would sure like to know. I am looking for a "renumber" function for a column of manually numbered rows -- this is one of the basic reasons why I need to use a software like Numbers at all. This works fine even in Microsoft Word (and one can fill a series incrementally in Excel), but apparently incremental series fill is not supported in Numbers. I noticed the helpful (albeit obscure way) of adding numbers sequentially by dragging -- but that does not work for thousands of rows that you need to repeat this action on every other minute while working with your document. Any more hint would be helpful (but inserting formulas into every single cell does not seem like a solution)! (I send this to Apple too.)
  • 5. Re: autofill without dragging fill handle?
    Level 8 Level 8 (41,760 points)
    If your numbers are in column B for instance, just insert the formula
    =ROW()

    Doing that when you created the document, you just would have to drag the formula on a short range.

    After that, when using the document, the formula will be created automatically when you add rows at the bottom or when you insert rows somewhere in the table.

    If you already have a large table, you may use this AppleScript:

    --[SCRIPT autoNumber]

    (*

    Enregistrer le script en tant qu'Application ou Progiciel : autoNumber.app
    déplacer l'application créée dans le dossier
    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

    Sélectionnez la première cellule à numéroter.

    menu Scripts > Numbers > autoNumber

    Saisir le numéro de départ.
    Le script remplit la cellule de départ et les cellules de rang supérieur dans la colonne ave la formule =LIGNE()-xx, xx étant calculé pour que la numérotation commence comme prévu.

    +++++++

    Save the script as an Application or an Application Bundle: autoNumber.app

    Move the newly created application into the folder:
    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

    Select the first cell to fill.

    menu Scripts > Numbers > autoNumber

    Type tha value of the starting number.
    The script fills selected cell and those of higher index in its column with the formula =ROW()-xx where xx is calculated given the starting number.

    Yvan KOENIG (Vallauris, FRANCE)
    7 février 2009
    *)

    property theApp : "Numbers"

    property delim : missing value
    property ROW_loc : missing value

    --=====

    on run
    if delim is missing value then
    if character 2 of (0.5 as text) is "." then
    set delim to ","
    else
    set delim to ";"
    end if
    set p2lproj to my getLproj(theApp)
    set ROW_loc to my getLocalizedFuncName(p2lproj, "ROW")
    end if

    set {rName, tName, sName, dName} to my getSelection()
    if rName is missing value then error "No selected cells"

    set twoNames to my decoupe(rName, ":")
    set {colNum1, rowNum1} to my decipher(item 1 of twoNames)



    (* Here we know the starting point of the destination area. *)
    tell application (path to frontmost application as string)
    if my parleFrancais() then
    set msg to text returned of (display dialog "Tapez le premier numéro à utiliser" default answer "1")
    else
    set msg to text returned of (display dialog "Enter the first number to use" default answer "1")
    end if
    end tell

    try
    set msg to msg as integer
    on error
    if my parleFrancais() then
    error "Nombre entier attendu !"
    else
    error "Must be an integer number !"
    end if
    end try

    set prem to rowNum1 - msg
    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName to tell column colNum1
    set cMax to (get count row)
    repeat with y from rowNum1 to cMax
    set value of cell y to "=" & ROW_loc & "()-" & prem
    end repeat
    end tell
    end run

    --=====

    on getSelection()
    local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection
    tell application "Numbers" to tell document 1
    set mySelectedRanges to selection range of every table of every sheet
    repeat with sheetRanges in mySelectedRanges
    repeat with thisRange in sheetRanges
    if contents of thisRange is not missing value then
    try
    --return thisRange --poorly formed result
    thisRange as text
    on error errmsg number errNum
    set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errmsg, quote)
    --set mySelection to (a reference to (range rn of table tn of sheet sn))
    return {myRange, myTable, mySheet, myDoc}
    end try
    end if -- contents…
    end repeat -- thisRange
    end repeat -- sheetRanges
    end tell -- document 1 of application

    return {missing value, missing value, missing value, missing value}
    end getSelection

    --=====

    on decipher(n)
    local letters, colNum, rowNum
    set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    if (character 2 of n) as text > "9" then
    set colNum to (offset of (character 1 of n) in letters) * 64 + (offset of (character 2 of n) in letters)
    set rowNum to (text 3 thru -1 of n) as integer
    else
    set colNum to offset of (character 1 of n) in letters
    set rowNum to (text 2 thru -1 of n) as integer
    end if
    return {colNum, rowNum}
    end decipher

    --=====

    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe

    --=====

    on getLocale(a, x)
    tell application a to return localized string x
    end getLocale

    --=====

    on getLocalizedFuncName(f, x)
    return localized string x from table "Localizable" in bundle file f
    end getLocalizedFuncName

    --=====

    on getLproj(aa)
    local lprojName
    set lprojName to my getlprojName(aa) & ".lproj"
    return (path to application support as text) & "iWork '09:Frameworks:SFTabular.framework:Versions:A:Resources:" & lprojName
    end getLproj

    --=====

    on getlprojName(aa)
    local lprojs, localId, lproj

    set lprojs to {{"da_DK", "da"}, {"nl_NL", "Dutch"}, {"en_US", "English"}, {"fi_FI", "fi"}, {"fr_FR", "French"}, {"de_DE", "German"}, {"it_IT", "Italian"}, {"ja_JP", "Japanese"}, {"ko_KR", "ko"}, {"no_NO", "no"}, {"pl_PL", "pl"}, {"pt_BR", "pt"}, {"pt_PT", "pt_PT"}, {"ru_RU", "ru"}, {"es_ES", "Spanish"}, {"sv_SE", "sv"}, {"zf_CN", "zh_CN"}, {"zh_TW", "zh_TW"}}

    if aa starts with "Pages" then (* why are they using different strings ? *)
    set localId to my getLocale(aa, "http://support.apple.com/manuals/#iwork")
    else (* here for Keynote or Numbers *)
    set localId to my getLocale(aa, "http://support.apple.com/en_US/manuals/#iwork")
    end if
    set localId to text (1 + (count of "http://support.apple.com/")) thru -1 of localId
    set localId to text 1 thru ((offset of "/" in localId) - 1) of localId

    set lproj to ""
    repeat with i from 1 to count of lprojs
    if localId is item 1 of item i of lprojs then
    set lproj to item 2 of item i of lprojs
    exit repeat
    end if
    end repeat

    if lproj = "" then
    if my parleFrancais() then
    error "Le fichier FrameWorks " & localId & " manque !"
    else
    error "The Frameworks file " & localId & "is missing !"
    end if
    else
    return lproj
    end if
    (* returns
    da
    Dutch
    English
    fi
    French
    German
    Italian
    Japanese
    ko
    no
    pl
    pt
    pt_PT
    ru
    Spanish
    sv
    zh_CN
    zh_TW
    given the language used to display the program's GUI. *)

    end getlprojName

    --=====

    on parleFrancais()
    local z
    try
    tell application theApp to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z = "Annuler")
    end parleFrancais

    --=====
    --[/SCRIPT]


    Yvan KOENIG (from FRANCE samedi 7 février 2009 19:55:27)
  • 6. Re: autofill without dragging fill handle?
    Level 8 Level 8 (41,760 points)
    Oops, I don't remember if you are using iWork '08 (the forum' title) or if you switched to iWork '09.

    Yvan KOENIG (from FRANCE samedi 7 février 2009 22:03:55)
  • 7. Re: autofill without dragging fill handle?
    eyeless Level 1 Level 1 (35 points)
    I switched to iWork '09 despite dislike of the new way "find" works and how slow it is, as I only have a few documents where it becomes hopeless to use "find" and those I guess I will have to keep opening in Excel until Apple fixes this problem. I especially like in iWork '09 the easy way of adding rows and columns inside a table (alt+arrows) (-I never saw such a thing in iWork '08). Your script looks impressive - maybe it works for iWork '09 too? /Jerry
  • 8. Re: autofill without dragging fill handle?
    Level 8 Level 8 (41,760 points)
    _It works only with Numbers '09._

    There is no script support for Numbers '08.

    This why I was afraid that it was not a good idea to post the script here

    Yvan KOENIG (from FRANCE samedi 7 février 2009 23:18:31)
  • 9. Re: autofill without dragging fill handle?
    Tom_Humphrey Level 1 Level 1 (5 points)
    was looking for similar as had to replace 5000+ cells with formulas and found a way in iwork 09 without writing a code.

    Basically highlight all the cells you want to fill with the cell with the formula at the top then go insert>fill>up/down/left/right (depending on where the highlighted cells are

    Done!
  • 10. Re: autofill without dragging fill handle?
    Level 8 Level 8 (41,760 points)
    Tom_Humphrey wrote:
    Basically highlight all the cells you want to fill with the cell with the formula at the top then go insert>fill>up/down/left/right (depending on where the highlighted cells are


    I'm not sure than highlighting the entire range is faster than dragging the handle

    Yvan KOENIG (from FRANCE jeudi 12 mars 2009 16:21:44)
  • 11. Re: autofill without dragging fill handle?
    Ric Donato Level 4 Level 4 (1,095 points)
    Hi Yvan,
    Thinking ahead I created one very large Numbers worksheet containing 20,000 rows ten columns. From that one worksheet, deleting rows, I created numerous smaller worksheets giving each a name denoting the total number of rows. Yes creating that first 20,000 row worksheet took a while but, from that one worksheet the creation of smaller ones was a snap.

    When I need many rows I call up a one of those worksheets that best fits the rows needed (using it as a template), any excess rows are removed. Cool huh.

    When I need to highlight thousands of rows I'll highlight the top most cell(s) then pull down the blue scroll bar to the final row do a Shift click on the final cell. That highlights the entire range then I do a Insert > Fill > Down. That, for me, is so much faster than creating from scratch a large worksheet. As you see, I am the lazy type

    It takes longer to type how I do it than it does to actually do it. Once your very large worksheet is created it's easy to cut off rows creating as needed smaller worksheets.

    Hope that helps someone.

    Cordially,

    RicD