Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    May 2012
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help defining 4700 hyperlinks

    I am a dedicated reader. Here though I need your help.
    In Excel 2010, I have a Spreadsheet with 4700 rows. I need to hyperlink each row with a file folder in the drive. The field from the Spreadsheet that needs the hyperlike contains only "part" of the file folder name. The file folder name would be like BAE5-064 Shell & Tube Exchanger. It is not difficult to hyperlink one row(cell) to a file folder doing one at a time. This is simple for me to do. But I need to do 4700 rows. (Yikes) I can't copy and paste because the numbers change from line to line in the excel rows. But, the other issue is that the file folder has a text title that is not in the excel row number. let me paste one here so you can see.

    Excel sample: Column 1 BAE5-064 Column 2 Group B Shell and Tube Heat Exchangers
    Now the file folder " BAE5-064 – Group B Shell & Tube Heat Exchangers - IFA"

    Each number in the rows will increase in numerical as well as some numbers will repeat. So how can I do a hyperlink formula for each line in the Spreadsheet? Without doing one line at a time.
    Thanks
    Donna
    Last edited by doorjam; 2012-05-10 at 12:10.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Donna

    Welcome to the Lounge.

    It is not too clear what you are asking.
    Perhaps you could look at the attached file, modify it, and then re-post it here with some additional notes on what you want.

    zeddy
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    May 2012
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    RE post Hyperlinks Help Needed

    Ok, lets see if I have this explained a little better. I was rushing out to a meeting before.

    Thank you very much for taking time to help me.

    Donna
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Have a look at this version attached.
    My hyperlink formula now uses a 'friendly' name.
    If you hover the mouse over one of the cells it will show the full underlying hyperlink location.
    Still not sure what you are after though.

    zeddy
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    May 2012
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you. That looks like what I have been doing. Now to the problem. How do I do that 4700 times? With out doing each one at a time?

    donna

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Donna

    If you look in column A, you will see how the hyperlink formula is defined.
    For example, put the cellpointer first in cell [A4], then use the down-arrow key to 'move' the cellpointer to cell [A5] i.e. putting the cellpointer into cell [A5] without clicking the cell and triggering the hyperlink.
    Now that you are in cell [A5], you can just press Ctrl-C to copy this formula and paste it down 4700 rows etc.

    Is that the kind of thing you need???

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    doorjam (2012-05-10)

  8. #7
    New Lounger
    Join Date
    May 2012
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes that works for some but not all...hmmm. See if you can do anything with this kind of numbering.. thank you so much for helping me.
    BAE5-140
    BAE5-141
    BAE5-142
    BAE5-142
    BAE5-143
    BAE5-144
    BAE5-144
    BAE5-144
    BAE5-144
    BAE5-145
    BAE5-146
    BAE5-147
    BAE5-148
    BAE5-149
    BAE5-152
    BAE5-153
    BAE5-153
    BAE5-153
    BAE5-153
    BAE5-153
    BAE5-153
    BAE5-153
    BAE5-153


    Donna

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Donna

    There shouldn't be a problem with having multiple duplicates like BAE5-153 displayed in column [A] as this is just the 'friendly' shortcut for the actual hyperlink. Each 'duplicate' in column [A] can still point to a different underlying folder location.
    Look at the formula again: My formula example gets the 'friendly' name to use from column [D]. The actual folders are made from columns [B] and [C] etc.

    zeddy

  10. #9
    New Lounger
    Join Date
    May 2012
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    STill trying to get the hyperlink to work.

    Ok, I am still not getting it. I am attaching several "live" rows. As well as the "live" folder locations. I want to thank you for trying to make an old gal understand this stuff. It is a new job for me and I am trying to do my best. Just need a little help from some techy friends. Your the best!!!!
    Attached Files Attached Files

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Donna

    In your example, I couldn't see where you could get the last bits for the actual folder locations.
    So, what I propose is to find all the subfolders in your network folder..
    J:\Shared A2B Folder\Lauren Transmittals\
    ..then extract the first 10 chars from these subfolders found in that network folder
    (e.g. BAE005-168)
    ..then try and match these extracted values with your entries in column [A].

    So, in the attached file, select sheet named [zeddy], and click the buttons 1, 2, and 3 in sequence.

    Let me know if this part works, then we can look at bringing the matching hyperlinks to your main sheet.

    zeddy
    Attached Files Attached Files

  12. #11
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Yes, i did get it to work. Although, I did have to edit the formula in A2, the last of the equation needed to have a 2 for it to get all the number. =LEN(B1)+2
    So, now I guess I need to try to match the numbers up? Hyperlinks with the row that has that number in it?

    Also, I am sorry for it taking so long to get back to you. I have been locked out and having a terrible time getting back in from my home computer. All is resolved now.
    Thank you for helping me.
    Donna

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Donna

    Great!
    So now, as you say, we need to match the numbers up.

    If you look at sheet [Sheet1] in the previous file I attached, scroll across to some 'spare' columns [AP] and [AQ].
    You will see I put in some formulas to check for a match, and to return the hyperlink if found.

    To be specific, the formula in cell [AP2] (which is copied down manually to match required number of rows)
    =IFERROR(MATCH(A2,zeddy!A:A,FALSE),"not found")
    ..says look for an exact match of the Transmittal No. in column [A] on the [zeddy] sheet.
    It will return the row number if it finds the match, otherwise, instead of displaying an error value, it displays "not found".
    (You will have to deal with these manually).

    In cell [AQ2], the formula
    =IFERROR(HYPERLINK(INDEX(zeddy!C:C,AP2)),A2)
    ..will create a hyperlink from the folder name in column [C] of sheet [zeddy] for the matching row.
    If the match row hasn't been found, this formula will simply return whatever you have for the Transmittal No. in column [A]

    So, perhaps you could use these formulas for your next step.
    It seems a lot of work, but for 4700 entries I'm sure it will be worth it.

    zeddy

  14. #13
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    help zeddy..still having the problem.

    Zeddy,
    I am sending your file back with a page 2 showing what I have. Its not working for me. I have a value that needs fixing.

    I love how the buttons worked on your spreadsheet. I want to learn to do that.


    Thank you agian for all your help.
    Donna
    Attached Files Attached Files

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,825
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Donna

    The message screen you are getting appears to be a "link" to sheet [zeddy].
    When you see the message again, click the [Cancel] button.
    Next, in the top Ribbon panel, select the [Data] ribbon group.
    In the Connections group, look for the 'pair of handcuffs' labelled "Edit Links".
    Click this.
    Then, in the dialog box that appears, click the [Break Link] button.
    Then re-save the file.

    Now, in this file you sent, the sub-folders begin with items like BAE5-158 (8 chars), not BAE005-158 (10 chars) as originally.
    To return 8 chars only rather than 10, change the 'hidden' formula in cell [A3] on sheet zeddy.
    Make sure the network source folder in cell [B1] ends with the backslash char \, then the formula in cell [A2] on sheet [zeddy] is = LEN(B1)+ 1
    (otherwise, with missing last \ char, formula is =LEN(B1)+2

    clear your entries and start it again??

    zeddy

  16. #15
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Thank you!!

    It took a little help from a couple of co workers, but it is up and working. Thank you so much for all your help. We could not have done it with out you.

    If ever I can help, I will pay it forward.

    Great Day!!!

Posting Permissions

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