10 Replies Latest reply: Feb 16, 2011 6:59 AM by KOENIG Yvan
Screaming.Pict Level 1 Level 1 (0 points)
Hi,

Was just wondering if this is possible. I can't see any way to do this.

I want to have a column of numbers and have it so that if I click on those numbers it sends me to a URL including that number.

E.G. Column displays;
1000
1001
1002

and if clicked on it actually hyperlinks to;
http://sum.url/1000
http://sum.url/1001
http://sum.url/1002
etc.

I presume that the only way to do this is to have a separate column that builds the URL from the first column, but that's not pretty and as a spoilt Apple user I demand pretty! And a pony!

Thanks.

MBP 15" 2.8GHz, Mac OS X (10.6.6)
  • Level 8 Level 8 (41,790 points)
    Screaming.Pict wrote:
    Hi,

    Was just wondering if this is possible. I can't see any way to do this.

    I want to have a column of numbers and have it so that if I click on those numbers it sends me to a URL including that number.

    E.G. Column displays;
    1000
    1001
    1002

    and if clicked on it actually hyperlinks to;
    http://sum.url/1000
    http://sum.url/1001
    http://sum.url/1002
    etc.


    Download *_iWork Formulas and Functions User Guide_*
    and study the function HYPERLINK.

    It may be a valid answer to your question, if your Url matches the Numbers requirements.

    Yvan KOENIG (VALLAURIS, France) mardi 15 février 2011 22:28:07
  • Screaming.Pict Level 1 Level 1 (0 points)
    Yvan,

    Thanks for the reply- I should have mentioned that I've already looked at the Hyperlink function- and that will allow me to enter the text in one column and create a hyperlink in another column.

    I want to do something rather more clever and have both in the same column- i.e. have some sort of a custom number format so that if I enter '1001' in the field it displays '1001' but also hyperlinks the same field to 'http://random.url/1001' or something like that.

    I think I'm probably being overly picky and should just accept having 2 separate columns- one to enter the text and one to produce the hyperlink.
  • Jerrold Green1 Level 7 Level 7 (29,945 points)
    S.P,

    It is simply not possible to have input and a formula in the same cell. Sometimes we can make it appear to happen by having a transparent input cell over a background formula cell of another table, but you want to access both parts, one for input and the other as a clickable link and they can't both be on top.

    Jerry
  • Screaming.Pict Level 1 Level 1 (0 points)
    Jerry,

    Thanks- that answers that. I vaguely remember doing something clever like this on Excel years ago using custom number formats that would allow the cell to hold one thing but show another.

    I'll stick with doing it with 2 columns then. Thanks.
  • Jerrold Green1 Level 7 Level 7 (29,945 points)
    S.P,

    You can get a transformation of input with Custom Format, but I'm pretty sure there's no formatting option for creating a link out of the reformatted input. That would make a neat feature request, but I wonder if there would be much interest amongst the users.

    Jerry
  • Level 8 Level 8 (41,790 points)
    Insert the numbers in an auxiliary table and put a formula in the main table
    or
    insert the numbers in column C, put formulas in column D then hide column C.
    or
    create an AppleScript used to enter the numerical value which will build then insert the wanted link displaying the number.

    I have some minutes available so I will build a quick and dirty skeleton doing that.

    Yvan KOENIG (VALLAURIS, France) mercredi 16 février 2011 14:21:45
  • Level 8 Level 8 (41,790 points)
    Here is the script.

    --

    --[SCRIPT calc&_storeHyperlink]
    (*
    Enregistrer le script en tant que Script, Application ou Progiciel : calc&_storeHyperlink.xxx
    déplacer le fichier créé 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.

    Placez le curseur dans la cellule que vous souhaitez alimenter.

    menu Scripts > Numbers > calc&_storeHyperlink

    La cellule pointée reçoit la formule calculant l'hyperlien à pointer.

    --=====

    L'aide du Finder explique:
    L'Utilitaire AppleScript permet d'activer le Menu des scripts :
    Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
    Cochez la case "Afficher le menu des scripts dans la barre de menus".

    +++++++

    Save the script as Script, Application or Application Bundle: calc&_storeHyperlink.xxx

    Move the newly created file 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.

    Put the cursor in the cell which must receive your entry.

    menu Scripts > Numbers > calc&_storeHyperlink

    The pointed cell receives the formula calculating the link to point to.

    --=====

    The Finder's Help explains:
    To make the Script menu appear:
    Open the AppleScript utility located in Applications/AppleScript.
    Select the "Show Script Menu in menu bar" checkbox.

    --=====

    Yvan KOENIG (VALLAURIS, France)
    2011/02/16
    *)

    property theApp : "Numbers"
    property link_root : "http://sum.url/"

    --=====

    on run

    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()

    tell application (path to frontmost application as string)
    if my parleAnglais() then
    set link_variable to text returned of (display dialog "Variable link’s component…" default answer "blabla bla")
    else
    set link_variable to text returned of (display dialog "Composant variable du lien…" default answer "blabla bla")
    end if
    end tell
    my doYourDuty(dName, sName, tName, rowNum1, colNum1, link_variable)

    end run

    --=====

    on doYourDuty(d, s, t, r, c, link_num) (*
    d = document's name
    s = sheet's name
    t = table's name
    r = rowIndex
    c = columnIndex
    link_num = variable link component *)
    local HYPERLINK_loc, delim_loc
    set HYPERLINK_loc to my getLocalizedFunctionName("Numbers", "HYPERLINK")
    set delim_loc to my getLocalizedDelimiter()
    tell application "Numbers" to tell document d to tell sheet s to tell table t
    set value of cell r of column c to "=" & HYPERLINK_loc & "(" & quote & link_root & link_num & quote & delim_loc & quote & link_num & quote & ")"
    end tell -- application …
    end doYourDuty

    --=====

    (*
    set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(dname,s_name,t_name,arange)
    *)
    on getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
    local two_Names, row_Num1, col_Num1, row_Num2, col_Num2
    tell application "Numbers"
    set d_Name to name of document d_Name (* useful if we passed a number *)
    tell document d_Name
    set s_Name to name of sheet s_Name (* useful if we passed a number *)
    tell sheet s_Name
    set t_Name to name of table t_Name (* useful if we passed a number *)
    end tell -- sheet
    end tell -- document
    end tell -- Numbers
    if r_Name contains ":" then
    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
    end if
    else
    set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, r_Name)
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    end if -- r_Name contains…
    return {row_Num1, col_Num1, row_Num2, col_Num2}
    end getCellsAddresses

    --=====
    (*
    set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
    *)
    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    return {d_Name, s_Name, t_Name, r_Name} & my getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
    end getSelParams

    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
    apply to named row or named column !
    *)
    on decipher(d, s, t, n)
    tell application "Numbers" to tell document d to tell sheet s to tell table t to ¬
    return {address of row of cell n, address of column of cell n}
    end decipher

    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection

    --=====

    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

    --=====
    (*
    Set the parameter delimiter which must be used in Numbers formulas
    *)
    on getLocalizedDelimiter()
    if character 2 of (0.5 as text) is "." then
    return ","
    else
    return ";"
    end if
    end getLocalizedDelimiter

    --=====

    on get_iWorkNum(a)
    local verNum
    tell application a to set verNum to item 1 of my decoupe(get version, ".")
    if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
    return "09"
    else
    return "11"
    end if
    end get_iWorkNum


    --=====
    (*
    Useful to get function's localized name if we need to build formulas
    examples:
    set OFFSET_loc to my getLocalizedFunctionName("Numbers", "OFFSET")
    set ADDRESS_loc to my getLocalizedFunctionName(theApp, "ADDRESS")
    set INDIRECT_loc to my getLocalizedFunctionName(theApp, "INDIRECT")
    *)
    on getLocalizedFunctionName(theApp, x)
    return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")
    end getLocalizedFunctionName

    --=====

    on getLocalizedName(a, x, f)
    tell application a to return localized string x from table "Localizable" in bundle file f
    end getLocalizedName

    --=====

    on parleAnglais()
    local z
    try
    tell application theApp to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z is not "Annuler")
    end parleAnglais

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


    Yvan KOENIG (VALLAURIS, France) mercredi 16 février 2011 15:15:08
  • Badunit Level 6 Level 6 (11,400 points)
    You can definitely create a format that turns it into a URL. It does not turn it into a hyperlink, though. I tried it just to be sure and it does not work. EDIT: I see Yvan has just posted a script.

    As an alternative, you could use a script. You select a cell and run the script (which could be done with a keyboard shortcut). The script turns it into a URL and opens your browser.

    I recommend using two columns, one with your text and one with the HYPERLINK function that converts it to a URL. You can resize the HYPERLINK column so the cell size is square and format it so it is a solid color or has an image of a button in it and the text is not visible.

    Message was edited by: Badunit
  • Screaming.Pict Level 1 Level 1 (0 points)
    @Badunit- Awesome idea! Functional and pretty.

    @Yvan- Blimey! Talk about going above and beyond the call of duty! Thanks, I'll try that out!
  • Level 8 Level 8 (41,790 points)
    Hello Badunit

    The HYPERLINK function give us the ability to display exactly what we want in the cell.
    So, my script ask the user to enter the number (or anything else)
    then it build and insert a formula designed to display only the entered value.



    In the screenshot you see the French formula.

    The English one is :
    =HYPERLINK("http://sum.url/1234","1234")

    If the OP dislike the scheme using a dialog belonging to the script to enter the value,
    here is an alternate version which requires that he enter the value in the cell then selecte it and trigger the script.

    --

    --[SCRIPT calc&_store_Hyperlinkalt]
    (*
    Enregistrer le script en tant que Script, Application ou Progiciel : calc&_storeHyperlink.xxx
    déplacer le fichier créé 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.

    Placez le curseur dans la cellule contenant la valeur à utiliser pour construire la formule LIEN.HYPERTEXTE.

    menu Scripts > Numbers > calc&_storeHyperlink

    La cellule pointée reçoit la formule requise.

    --=====

    L'aide du Finder explique:
    L'Utilitaire AppleScript permet d'activer le Menu des scripts :
    Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
    Cochez la case "Afficher le menu des scripts dans la barre de menus".

    +++++++

    Save the script as Script, Application or Application Bundle: calc&_storeHyperlink.xxx

    Move the newly created file 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.

    Put the cursor in the cell which store the value which must be used to build the HYPERLINK formula.

    menu Scripts > Numbers > calc&_storeHyperlink

    The pointed cell receives the formula calculating the link to point to.

    --=====

    The Finder's Help explains:
    To make the Script menu appear:
    Open the AppleScript utility located in Applications/AppleScript.
    Select the "Show Script Menu in menu bar" checkbox.

    --=====

    Yvan KOENIG (VALLAURIS, France)
    2011/02/16
    *)

    property theApp : "Numbers"
    property link_root : "http://sum.url/"

    --=====

    on run

    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()

    my doYourDuty(dName, sName, tName, rowNum1, colNum1)

    end run

    --=====

    on doYourDuty(d, s, t, r, c) (*
    d = document's name
    s = sheet's name
    t = table's name
    r = rowIndex
    c = columnIndex*)
    local HYPERLINK_loc, delim_loc, link_num
    set HYPERLINK_loc to my getLocalizedFunctionName("Numbers", "HYPERLINK")
    set delim_loc to my getLocalizedDelimiter()
    tell application "Numbers" to tell document d to tell sheet s to tell table t
    set link_num to value of cell r of column c as integer
    set value of cell r of column c to "=" & HYPERLINK_loc & "(" & quote & link_root & link_num & quote & delim_loc & quote & link_num & quote & ")"
    end tell -- application …
    end doYourDuty

    --=====

    (*
    set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(dname,s_name,t_name,arange)
    *)
    on getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
    local two_Names, row_Num1, col_Num1, row_Num2, col_Num2
    tell application "Numbers"
    set d_Name to name of document d_Name (* useful if we passed a number *)
    tell document d_Name
    set s_Name to name of sheet s_Name (* useful if we passed a number *)
    tell sheet s_Name
    set t_Name to name of table t_Name (* useful if we passed a number *)
    end tell -- sheet
    end tell -- document
    end tell -- Numbers
    if r_Name contains ":" then
    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
    end if
    else
    set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, r_Name)
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    end if -- r_Name contains…
    return {row_Num1, col_Num1, row_Num2, col_Num2}
    end getCellsAddresses

    --=====
    (*
    set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
    *)
    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    return {d_Name, s_Name, t_Name, r_Name} & my getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
    end getSelParams

    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
    apply to named row or named column !
    *)
    on decipher(d, s, t, n)
    tell application "Numbers" to tell document d to tell sheet s to tell table t to ¬
    return {address of row of cell n, address of column of cell n}
    end decipher

    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection

    --=====

    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

    --=====
    (*
    Set the parameter delimiter which must be used in Numbers formulas
    *)
    on getLocalizedDelimiter()
    if character 2 of (0.5 as text) is "." then
    return ","
    else
    return ";"
    end if
    end getLocalizedDelimiter

    --=====

    on get_iWorkNum(a)
    local verNum
    tell application a to set verNum to item 1 of my decoupe(get version, ".")
    if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then
    return "09"
    else
    return "11"
    end if
    end get_iWorkNum


    --=====
    (*
    Useful to get function's localized name if we need to build formulas
    examples:
    set OFFSET_loc to my getLocalizedFunctionName("Numbers", "OFFSET")
    set ADDRESS_loc to my getLocalizedFunctionName(theApp, "ADDRESS")
    set INDIRECT_loc to my getLocalizedFunctionName(theApp, "INDIRECT")
    *)
    on getLocalizedFunctionName(theApp, x)
    return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")
    end getLocalizedFunctionName

    --=====

    on getLocalizedName(a, x, f)
    tell application a to return localized string x from table "Localizable" in bundle file f
    end getLocalizedName

    --=====

    on parleAnglais()
    local z
    try
    tell application theApp to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z is not "Annuler")
    end parleAnglais

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


    Yvan KOENIG (VALLAURIS, France) mercredi 16 février 2011 15:59:13