Nothing bizarre.
Some formulas aren’t robust enough to support a sort.
Using OFFSET as I do is a way to have a robust formula.
The original formula is easy to understand as you described it
The new one does exactly the same thing : get the value just above the cell where ii is inserted.
The difference is that it no longer embed a reference to the hosting cell which the original one did.
Honestly I don’t know what is wrong.
Was it the original one which benefit of a hole in syntax checking ” there is a defined error message flagging formulas embedding a reference to the hosting cell
or
Is it a change in the system which forbid now what was accepted ?
I’m really surprised to see that a change in the system modify the behavior of the syntax checker but I have no mean to find an explanation so I just edit my formulas to match the new rule.
Using the cell five columns to the left of the hosting cell as origin for coordinates used by OFFSET is purely an arbitrary choice.
I refused to use the cell just on the left because it grab the value of the hosting cell.
Maybe I will edit again and use the cell in column 1 but I’m wondering if it’s really a good idea.
I may have to insert new columns between the column A and the column F while I’m sure that there will always be the offset 5 between the column which are F and K at this time so the “surprising” choice is more robust than the alternate “logical” one.
I’m quite at ease whith that because as an engineer I am accustomed to change the origin according to the needs.
When I defined the location of an electric switch in a room at the 14th floor, I didn’t calculated it starting from the ground level 
In France we are using a boundary-stone near the cathedral “Notre dame de Paris” as the origin for distances carved on milestones.
But when a contractor build an house in an estate, he is not fool enough to measure starting from Paris
I’m always surprised when I read that English users are surprised by the fact that spreadsheet functions names are localised. They were in AppleWorks, they are in Excel, in openOffice, in libreOffice, in iWork and now in iPlay.
It’s the same for the decimal delimiter, for the delimier used in spreadsheet formulas and for the delimiter used in CSV files.
If you look in the posted formulas, you will see that the used delimiter is semi-colon while English formulas use the comma.
This is why, in several of my scripts, I use these handlers to build non localization dependant scripts:
#=====
(*
Set the parameter delimiters which must be used in Numbers formulas
set {delim, deci} to my get2LocalizedDelimiters()
*)
on get2LocalizedDelimiters()
if character 2 of (0.5 as text) is "." then
return {",", "."}
else
return {";", ","}
end if
end get2LocalizedDelimiters
#=====
(*
Useful to get function's localized name if we need to build formulas
examples:
set OFFSET_loc to my getLocalizedTabularString("Numbers", "OFFSET")
set ADDRESS_loc to my getLocalizedTabularString("Numbers", "ADDRESS")
set INDIRECT_loc to my getLocalizedTabularString("Numbers", "INDIRECT")
set Unnamed_Table_loc to my getLocalizedTabularString("Numbers", "Unnamed Table")
Requires :
decoupe()
get_iWorkNum()
*)
on getLocalizedTabularString(theApp, x)
local path2app
activate application theApp
tell application "System Events"
set path2app to path of (get application file of process theApp)
exists folder (path2app & "Contents:Frameworks:SFTabular.framework")
end tell
if result then
path2app & "Contents:Frameworks:SFTabular.framework:Versions:A:Resources:"
else
(path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:"
end if
tell application theApp to return localized string x from table "Localizable" in bundle file result
end getLocalizedTabularString
#=====
Maybe learning that will help you why I’m sometimes nervous when I am blamed because I use French varnames.
I never saw an other coder taking care of localization problems in scripts as I do.
Even Bill Cheeseman’s UI Browser is English specific and it’s why I refuse to buy it.
The scripts which it deliver must be heavily edited if we want to take care of non-English users.
On my side, varnames are often french-looking ” and will continue to be ” but when my scripts are supposed to build formulas for Numbers, they deliver ones matching the user’s language settings.
When I post a script using GUIscripting, I do my best to deliver script running correctly on differently localized systems.
Who is doing that except myself?
Yvan KOENIG (VALLAURIS, France) mardi 29 octobre 2013 10:10:41
