relative refs
Here is my re-invention of the wheel, for those on Macs. It lets you do a basic 'fill down' or 'fill across' for VERY simple formulas. I used this, then a search-replace in BBEdit to add fancy stuff like FIXED() or whatever. -- start AppleScript (you'll have replace th <br>'s with returns)" -----------<br> display dialog "This script is to create some the effect of a relative cell reference for Numsum.com, which doesn't have it yet." & return & return & "It works, for VERY simple formulas, by creating a list of formulas separated by returns (to fill down) or tabs (to fill to the right). When you paste the list into the first cell, it populates the bunch of them."<br> <br> set f1 to text returned of (display dialog "Enter (or better yet, paste) the formula for your first cell:" default answer "")<br> display dialog "How many cells (including your starting cell) do you want to fill?" buttons {"Cancel", "Horizontal", "Vertical"} default answer "" default button 3<br> set {cellsToFill, stepDirection} to {text returned of the result, button returned of the result}<br> <br> if stepDirection is "Vertical" then<br> set cellSep to return<br> else<br> set cellSep to tab<br> end if<br> <br> considering case<br> set capAlpha to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"<br> <br> set parsedFormula to {} -- example for "=F2/C2": {"=", "F2", "/", "C2"}<br> set cellFlagList to {} -- {false, true, false, true}<br> set thisItem to ""<br> set cellFlag to false<br> set k to 1<br> repeat while k ≤ length of f1<br> if character k of f1 is in capAlpha then<br> --Big fat assumptions here:<br> -- • All capital letters signify the start of a cell reference. This obviously fails when the formula contains functions like FIXED<br> -- • All cell references are 2 characters (i.e. nobody will expect this to work starting in any cell further out than Z9)<br> set cellFlag to true<br> set thisItem to text k through (k + 1) of f1<br> set k to k + 1<br> else<br> set cellFlag to false<br> set thisItem to character k of f1<br> end if<br> copy thisItem to end of parsedFormula<br> copy cellFlag to end of cellFlagList<br> set k to k + 1<br> end repeat<br> end considering<br> <br> set fOld to f1<br> set fString to fOld<br> repeat with addMe from 1 to cellsToFill<br> set fNew to ""<br> repeat with n from 1 to (length of parsedFormula)<br> set thisPiece to item n of parsedFormula<br> set cellFlag to item n of cellFlagList<br> if cellFlag is false then<br> set fNew to fNew & thisPiece<br> else<br> set fNew to fNew & incrementCell(thisPiece, addMe, "Vertical")<br> end if<br> end repeat<br> set fString to fString & cellSep & fNew<br> set fOld to fNew<br> end repeat<br> <br> set the clipboard to text of fString<br> display dialog "The clipboard now contains:" & return & return & fString & return & return & "You can paste this into the same cell you copied the initial formula from." buttons {"Cool."} default button 1<br> -------------------------------------------------------<br> to incrementCell(c, newCoord, HorV)<br> --Big fat assumption here:<br> --The number might have incremented past 9 but the letter won't go past Z.<br> set c1 to character 1 of c<br> if HorV is "Vertical" then -- change A1 to A2:<br> set c to c1 & (((text 2 through -1 of c) as integer) + newCoord)<br> else -- change A1 to B1:<br> set c1 to ASCII character of ((ASCII number of c1) + newCoord)<br> set c to c1 & (text 2 through -1 of c)<br> end if<br> return c<br> end incrementCell<br> -----------------------------------
2420 days ago 

relative refs
Hi, sorry, not yet on relative cell refs.
2429 days ago