Results 1 to 15 of 15
Thread: Help defining 4700 hyperlinks

20120510, 09:53 #1
 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 BAE5064 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 BAE5064 Column 2 Group B Shell and Tube Heat Exchangers
Now the file folder " BAE5064 – 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
DonnaLast edited by doorjam; 20120510 at 12:10.

20120510, 10:42 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 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 repost it here with some additional notes on what you want.
zeddy

20120510, 12:28 #3
 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

20120510, 14:15 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 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

20120510, 14:19 #5
 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

20120510, 14:36 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 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 downarrow 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 CtrlC to copy this formula and paste it down 4700 rows etc.
Is that the kind of thing you need???
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
doorjam (20120510)

20120510, 18:00 #7
 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.
BAE5140
BAE5141
BAE5142
BAE5142
BAE5143
BAE5144
BAE5144
BAE5144
BAE5144
BAE5145
BAE5146
BAE5147
BAE5148
BAE5149
BAE5152
BAE5153
BAE5153
BAE5153
BAE5153
BAE5153
BAE5153
BAE5153
BAE5153
Donna

20120511, 05:18 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 Posts
Hi Donna
There shouldn't be a problem with having multiple duplicates like BAE5153 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

20120511, 08:06 #9
 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!!!!

20120511, 17:37 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 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. BAE005168)
..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

20120513, 09:15 #11
 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

20120514, 05:09 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 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

20120514, 17:09 #13
 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

20120514, 18:06 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,956
 Thanks
 140
 Thanked 522 Times in 498 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 resave the file.
Now, in this file you sent, the subfolders begin with items like BAE5158 (8 chars), not BAE005158 (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

20120522, 10:46 #15
 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!!!