I'm trying to make a link in Numbers so when I click a cell it redirects me to another sheet (sort of a hyperlink). When I try the 'hyperlink' function it only lets me link to a website or email. Any ideas?
That is not a feature of Numbers. The Sheets pane is always in view. Just click on the Sheet that you wish to visit.
Jerry
OK - Thanks for that. I'll stop looking now. Any idea why?
Not comparing this to Excel, but in their hyperlink function you just specify the cell OR the URL you want to link to and it works nicely.
patten wrote:
OK - Thanks for that. I'll stop looking now. Any idea why?
Not comparing this to Excel, but in their hyperlink function you just specify the cell OR the URL you want to link to and it works nicely.
I think it's just that Numbers was always intended to be a light-duty app with a slimmer feature set. Find works well (Command-F). It gives you a pane at the bottom of the window with a clickable list of hits that act like hyperlinks.
Jerry
I think an Applescript was written that gives "hyperlink to a cell" functionality. The differences between this and Excel are 1) the script is not part of the document, it needs to be installed separately and on all Macs that need the functionality and 2) instead of just clicking on the cell, you would click on the cell then use a keyboard shortcut to activate the script and take you to the "hyperlinked" cell. If you are interested, I'll take a look.
It didn't take long to find.
If you want to try it out, open Applescript Editor and paste in the script from below. Format a cell in your table as text and type something like Table 1::D2 or Sheet 2::Table 1::D2. Click on the cell. Go to Applescript Editor and click Run.
If you like it, you can use Automator to make a service from it and then you can assign a keyboard shortcut to the service. I can walk you through it if you are interested.
--[SCRIPT fakeLinkTo]
(*
Enregistrer le script en tant que Script : fakeLinkTo.scpt
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.
Pour créer un lien vers une cellule,
insérer dans une cellule de la table d'appel la formule
=table cible :: cellule cible
ou
=feuille cible :: table cible :: cellule cible.
Fixer le format de la cellule au mode texte.
Sélectionner la cellule contenant le 'lien'
menu Scripts > Numbers > fakeLinkTo
Si nécessaire, le script active la feuille cible (ça prend un certain temps)
puis sélectionne la cellule ciblée.
+++++++++
Save the script as Script : fakeLinkTo.scpt
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.
To create a link to a cell,
insert in a cell of the starting table the formula :
= target table :: target cell
or
= target sheet :: target table :: target cell
Set the cell's format to text.
Select the cell containing the 'link'.
go to the Scripts Menu, choose Numbers, then choose fakeLinkTo
If it's required, the script activates the target sheet (which may take a few seconds)
then select the target cell.
Yvan KOENIG (Vallauris, FRANCE)
2010/07/15
*)
--=====
property theApp : "Numbers"
property twoColons : "::"
--=====
on run
my activateGUIscripting()
set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
set cible to value of cell rowNum1 of column colNum1
end tell
if cible starts with "=" then set cible to text 2 thru -1 of cible
repeat while first character of cible is in {quote, space} --, "'"}
set cible to text 2 thru -1 of cible
end repeat
repeat while last character of cible is in {quote, space} --, "'"}
set cible to text 1 thru -2 of cible
end repeat
if cible contains twoColons then (*
Here entry is "sheet :: table :: cell" or "table :: cell" *)
if cible contains twoColons & space then set cible to my recolle(my decoupe(cible, twoColons & space), twoColons)
if cible contains space & twoColons then set cible to my recolle(my decoupe(cible, space & twoColons), twoColons)
set cible to my decoupe(cible, twoColons)
if (count of cible) = 2 then (* was "table :: cell" *)
set {tName, rname} to cible
else (*
the 'link' was "sheet :: table :: cell" or "sheet :: table :: cell :: extraneous datas"
keep only the three first items *)
set {sName, tName, rname} to items 1 thru 3 of cible
my selectSheet(dName, sName)
end if -- count of cible
set cible to rname
end if -- cible contains…
repeat while sName starts with "'"
set sName to text 2 thru -1 of sName
end repeat
repeat while sName ends with "'"
set sName to text 1 thru -2 of sName
end repeat
repeat while tName starts with "'"
set tName to text 2 thru -1 of tName
end repeat
repeat while tName ends with "'"
set tName to text 1 thru -2 of tName
end repeat
repeat while cible starts with "'"
set cible to text 2 thru -1 of cible
end repeat
repeat while cible ends with "'"
set cible to text 1 thru -2 of cible
end repeat
my doYourDuty(dName, sName, tName, cible)
end run
----=====
on doYourDuty(d, s, t, c)
try
tell application "Numbers" to tell document d to tell sheet s to tell table t
set selection range to cellc
end tell
on error
if my parleAnglais() then
error "cell “" & s & " " & twoColons & " " & t & " " & twoColons & " " & c & "” unavailable !"
else
error "cellule «" & s & " " & twoColons & " " & t & " " & twoColons & " " & c & "» inexistante !"
end if
end try
end doYourDuty
--=====
(*
set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(d_name,s_name,t_name,a_range)
*)
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 documentd_Name(* useful if we passed a number *)
tell document d_Name
set s_Name to name of sheets_Name(* useful if we passed a number *)
tell sheet s_Name
set t_Name to name of tablet_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 recolle(l, d)
local t
set AppleScript's text item delimiters to d
set t to l as text
set AppleScript's text item delimiters to ""
return t
end recolle
--=====
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 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
--=====
on activateGUIscripting()
(* to be sure that GUI scripting will be active *)
tell application "System Events"
set (UI elements enabled) to true
end tell
end activateGUIscripting
--=====
(*
==== Uses GUIscripting ====
*)
on selectSheet(theDoc, theSheet)
(*
most of this handler is from Nigel Garvey
*)
local maybe, targetSheetRow
try
tell application "Numbers"
activate
set theDoc to name of document theDoc
(* useful if the passed value is a number. Checks also that we passed the name of an open doc *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The spreadsheet “" & theDoc & "” is not open !"
else
error "Le tableur « " & theDoc & " » n’est pas ouvert ! "
end if -- my parleAnglais
end try
try
tell application "Numbers" to tell document theDoc
set theSheet to name of sheet theSheet
(* useful if the passed value is a number. If we passed a string, checks that the sheet “theSheet” exists *)
end tell -- Numbers
on error
if my parleAnglais() then
error "The sheet “" & theSheet & "” is unavailable in the spreadsheet “" & theDoc & "” !"
else
error "La feuille « " & theSheet & " » n’existe pas dans le tableur « " & theDoc & " » ! "
end if -- my parleAnglais
end try
set maybe to 5 > (system attribute "sys2")
tell application "System Events" to tell application process "Numbers"
tell outline 1 of scroll area 1 of splitter group 1 of splitter group 1 of windowtheDoc
if maybe then (* macOS X 10.4.x
'(value of attributes contains 0)': '(value of attribute "AXDisclosureLevel" is 0)' sometimes works in Tiger, sometimes not.
The only possible instances of 0 amongst the attributes are the disclosure level of a sheet row and the index of the first row, which represents a sheet anyway.
Another possibility is '(value of attribute -1 is 0)', which makes me uneasy. *)
set targetSheetRow to first row where ((value of attributes contains 0) and (value of first static text is theSheet))
else (* macOS X 10.5.x or higher *)
set targetSheetRow to first row where ((value of attribute "AXDisclosureLevel" is 0) and ((groups is {}) and (value of first static text is theSheet)) or (value of first group's first static text is theSheet))
end if -- maybe…
tell targetSheetRow to set {value of attribute "AXSelected", value of attribute "AXDisclosing"} to {true, true}
-- Focus the "Sheets" column ('outline 1 …') AFTER the target row is selected.
set value of attribute "AXFocused" to true
delay 0.1
set maybe to (get value of attribute "AXPosition" of targetSheetRow)
end tell -- outline…
end tell -- System Events
end selectSheet
--=====
--[/SCRIPT]
--
A little over my head, unfortunately.
Thanks for the time and effort though.
Nah, it's simple. Difficult to write, for sure, but easy to cut & paste. You don't have to understand how it works.
how do I get a cell to redirect me to another sheet?
