isikus

Q: Conditional hyperlinks to cells in Numbers spreadsheet

I'm working on a project that consists of multiple spreadsheets for Excel, LibreOffice Calc and Numbers. The ones for Calc and Numbers are based on the Excel one because I have the biggest experience in it. So one of the main functions that I use is HYPERLINK, which makes hyperlinks to the cells of the spreadsheet, and I found out that it won't work as expected in Numbers because the cell linking is not provided by this function here. Moreover, I use HYPERLINK to create conditional hyperlinks, e. g. =IF(A1=1;HYPERLINK("#C29";"Example");"") is a hyperlink only if A1 is 1 - that means that a static hyperlink can't solve my problem.

 

So is there any way to create a conditional hyperlink to any other cell in the same spreadsheet in Numbers?

Numbers (iWork)

Posted on Nov 26, 2013 8:51 AM

Close

Q: Conditional hyperlinks to cells in Numbers spreadsheet

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Nov 26, 2013 8:56 AM in response to isikus
    Level 6 (19,282 points)
    iWork
    Nov 26, 2013 8:56 AM in response to isikus

    isikus,

     

    The purpose of the hyperlinks is simply to navigate that spreadsheet?  What is the purpose of the hyperlinks?  Aside from the hyperlink problem what ARE you trying to solve?

     

    From the Functions description:

    The HYPERLINK function creates a clickable link that opens a webpage or new email message.

    • HYPERLINK(url, link-text)
      • url: A standard universal resource locator. url is a string value that must contain a properly formatted universal resource locator string.
      • link-text: An optional string value that specifies the text that appears as a clickable link in the cell.

      • If link-text is omitted, url is used as link-text.

     

    So I do not think hyperlinks will for for you in Numbers.

  • by isikus,

    isikus isikus Nov 26, 2013 9:22 AM in response to Wayne Contello
    Level 1 (0 points)
    Nov 26, 2013 9:22 AM in response to Wayne Contello

    In Excel use HYPERLINK not to create a link to an URL but to create a link to an other cell is=n the same document. What I want is if a person clicks (picture 2) on the cell with the function (picture 1), the hyperlink makes him jump to the specific cell which I want him to jump to (picture 3). As you see, the usage of this function in Excel is extended:

    HYPERLINK(link_location,friendly_name)

    The HYPERLINK function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

    • Link_location    This is the path and file name to the document to be opened. Link_location can refer to a place in a document — such as a specific cell or named range in an Excel worksheet or workbook

    So it seems that the Numbers HYPERLINK function won't help me, will it? Is it possible then to make Numbers work the same anyhow?

     

     

    Picture 1: Typing the formula in Excel

    3.png

     

    Picture 2: Clicking the cell A1 with the formula

    2.png

     

    Picture 3: Now the active cell is AA100Untitled.png

  • by Badunit,Helpful

    Badunit Badunit Nov 26, 2013 9:31 AM in response to isikus
    Level 6 (11,705 points)
    iTunes
    Nov 26, 2013 9:31 AM in response to isikus

    Numbers' Hyperlink function does not have the ability to hyperlink to a cell or anything else within the document.

     

    When Applescript was supported, there was a script available that would allow you to "go to" a cell. The cell address would be in a cell and you would use a keystroke to active the script and take you to the new cell.  Unfortunately, no AppleScript right now in Numbers 3.0

  • by isikus,

    isikus isikus Nov 26, 2013 9:34 AM in response to Badunit
    Level 1 (0 points)
    Nov 26, 2013 9:34 AM in response to Badunit

    Oh, I see, but maybe there is any other way to make a hyperlink to a cell in Numbers except using function HYPERLINK? I suppose there aren't.

  • by SGIII,

    SGIII SGIII Nov 26, 2013 7:35 PM in response to isikus
    Level 6 (10,720 points)
    Mac OS X
    Nov 26, 2013 7:35 PM in response to isikus

    We're keeping our fingers crossed this kind of thing will once again be possible when Apple reintroduces a degree of AppleScript support sometime in the coming months.

     

    SG

  • by SallyT.,

    SallyT. SallyT. Feb 24, 2014 1:10 PM in response to isikus
    Level 1 (4 points)
    Feb 24, 2014 1:10 PM in response to isikus

    I would also like to be able to create hyperlinks in my spreadsheets that take me to other pages within the document.  I am new to Mac's and Numbers.  Up until now I've been using Excel to keep track of the photographs I have sold licences for and how much I have received in royalties.  I have an index/summary page as my first sheet on which I have the titles of all the photos I've sold.  Each photo then has it's own sheet within the document and the title on the front index sheet is hyperlinked to the photo's individual page.  That way it saves me scrolling through endless tabs to find the right sheet.  Not being able to create a hyperlink like this is a bit of a show stopper for me and probably means I am going to have to fork out for a Mac version of MS Office, something I really don't want to do. 

     

    Sally

  • by SGIII,

    SGIII SGIII Feb 24, 2014 1:24 PM in response to SallyT.
    Level 6 (10,720 points)
    Mac OS X
    Feb 24, 2014 1:24 PM in response to SallyT.

    Hi SallyT.,

     

    I'm not sure Numbers is designed to be a repository for photos.  That could make for some very large documents!

     

    Have you considered storing the photos in Dropbox or similar, then copy pasting the hyperlink to each photo into your Numbers document?  That way you can do your math in Numbers, which is what spreadsheets are best at, and still be able to call up each photo as needed.

     

    SG

  • by billfromarlington,

    billfromarlington billfromarlington Mar 6, 2014 11:18 AM in response to SallyT.
    Level 1 (0 points)
    Mar 6, 2014 11:18 AM in response to SallyT.

    Sally-

    I have stumbled upon this feature in numbers which seems to work quite well for me. I manage a construction project and need to have several .pdf items "at the ready" to view alongside my spreadsheet data. The following works for me across several macs using the numbers spreadsheet:

     

    1. find the .pdf .png, etc item you want to link to in the spreadsheet

    2. click and drag it to the safari icon in the dock - this will generate a "browser address" in safari

    3. Copy and paste that link to the spreadsheet cell you want to use as the link

    4. To access the item (.pdf, .png etc) highlight the text in the cell and right click and you will see "open URL", select that and there the item is opened in preview.

     

    I've found that when the docs are saved in preview in icloud, I can go from one mac to another open my spreadsheet and the links still work.

     

    Try it, this is as close as Ive come to the links function in Excel.

    BB

  • by SGIII,

    SGIII SGIII Mar 6, 2014 11:57 AM in response to billfromarlington
    Level 6 (10,720 points)
    Mac OS X
    Mar 6, 2014 11:57 AM in response to billfromarlington

    BB,

     

    Interesting post. Thanks. I can't get by this message on my machine why I choose 'Open URL' per your instructions:

     

    Screen Shot 2014-03-06 at 2.33.20 PM.png

     

    (I do have permissions to open the file.)

     

    But I can open the file by selecting the cell with the "browser address" (file:///...) and running the script below.

     

    When you have docs saved by Preview in iCloud what does the "browser address" for those look like?

     

    SG

     

     

    --opens a file whose address is in the currently selected cell in Numbers 3

    tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)

              set theURL to value of the first cell of the selection range

              tell application "System Events" to do shell script "open " & quoted form of theURL

    end tell

    --end of script

  • by Blue5fingers,

    Blue5fingers Blue5fingers Jun 30, 2014 10:27 AM in response to SGIII
    Level 1 (5 points)
    Jun 30, 2014 10:27 AM in response to SGIII

    The need to use hyperlinks that connect different cells is, in my experience, a basic requirement that should have been included in Numbers.  Apple seems to be off the pace, which is a shame because I would like to ditch Excel.

  • by Jerrold Green1,

    Jerrold Green1 Jerrold Green1 Jun 30, 2014 11:57 AM in response to Blue5fingers
    Level 7 (30,001 points)
    Jun 30, 2014 11:57 AM in response to Blue5fingers

    Send Feedback to Apple via the tool in the Numbers menu.

     

    Jerry

  • by Blue5fingers,

    Blue5fingers Blue5fingers Aug 5, 2014 1:43 AM in response to Jerrold Green1
    Level 1 (5 points)
    Aug 5, 2014 1:43 AM in response to Jerrold Green1

    Hi Jerry,

     

    Thanks - I didn't know about the feedback tool.  Feedback now sent.

     

    Kind regards,

     

    B5F