+ Reply to Thread
Results 1 to 4 of 4

Count hyperlinks

  1. #1
    Registered User
    Join Date
    11-06-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    2

    Count hyperlinks

    Dear all,

    I have an excel worksheet like the following (there are about 20 cols and 500 rows)

    Col h Col I Col J Col k Col L Col M
    3/12/2013 Rob 1/2/2014 Susanna 3/3/2014 Tash
    8/2/2014 Tash 19/11/2014 colin

    Some of the dates has hyperlink associate to it, ie, not all date have hyperlink

    Is there a way I can use formular to count number of hyperlink according to user defined time range, say before 1/1/2014, between 1/1/2014 and 30/10/2014, after 30/10/2014. So I may have the following results

    before 1/1/2014
    Tash 1
    Colin 0
    Rob 0
    Suzanna 3

    between 1/1/2014 and 30/10/2014
    Tash 22
    Colin 7
    Rob 90
    Suzanna 23



    after 30/10/2014
    Tash 10
    Colin 2
    Rob 1
    Suzanna 3

    Is the above achieveable using formular please?

    Thanks in advance.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count hyperlinks

    If you want to count dates between to specified dates, you can use countifS(). However, if some of those dates are hyperlinked, and others not, I know of no regular formula that will be abke to tell the difference.

    Formulas work on what the cell displays (generally), and not on what the cell contents are (formulas etc)

    For instance, assume you have a cell (A1) that contains =10*10 It will display 100, if you used =left(A1,2) you would get 10, not =1...it is reading the 1st 2 characters of the answer, not the cell contents

    Hope that helps?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Count hyperlinks

    You can extract that 10 out of the =10*10 like this...

    =RIGHT(LEFT(FORMULATEXT(REFERENCED CELL WITH FORMULA),3),2)

    That would pull the formula built into the cell and then give you first the left 3 and then the right 2 leaving you with 10

    Now with all that said, would you mind posting a small book/sample file so that I can see the dates, formats etc...

    Unless of course that formula gives you what you need
    -If you think you are done, Start over - ELeGault

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count hyperlinks

    FORMULATEXT() is a function that (Im assuming) is available in 2013? It is not available in 2010 or, (I doubt) any earlier versions, and OP indicates they have 2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Hyperlinks in Powerpoint Error - Hyperlinks stripped
    By memsimpson in forum Excel General
    Replies: 1
    Last Post: 05-27-2014, 01:55 PM
  2. Find Hyperlinks, Copy Hyperlinks to alternative sheet, print all hyperlinks
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 05:13 PM
  3. Replies: 7
    Last Post: 06-14-2012, 09:44 AM
  4. How can I count Hyperlinks?
    By samualt in forum Excel General
    Replies: 2
    Last Post: 04-21-2007, 06:29 PM
  5. Replies: 0
    Last Post: 05-14-2006, 11:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1