|
I have a workbook with several sheets of data. The first sheet has a list of company names in alphabetical order like this: The remaining sheets have the actual data sets for each company and look like this: What I'd like to do is hyperlink the names on the first sheet to the coresponding data sets on the following sheets. I know I can use the "Create Hyperlink" menu when I right click a cell but with several hundred names it'll take a while. I also played around with the "HYPERLINK" formula but I couldn't seem to get it to copy the data horizonally when I tried to autofill the cells on the first sheet vertically. Also the HYPERLINK formula only change the second part of the formula "friendly name" when I draged it. It did not change the first part of the formula "link location". I would need it to change both fields when I dragged it so it not only updated the link location but also the "friendly name" of the link.
|
# ¿ Jun 20, 2011 04:44 |
|
|
# ¿ Apr 28, 2024 00:44 |
|
Sub Par posted:You can accomplish this fairly easily with a macro. Try this: I don't know if it matters, but on the first sheet when I want to place all the hyperlinks, each column has a different number of names in it. So in the second loop I gave the row range of the column that has the largest amount of names in it.
|
# ¿ Jun 21, 2011 15:49 |
|
OK, so I replaced the code but now I get the following error: Is there a way to find out what exactly the error is?
|
# ¿ Jun 22, 2011 19:01 |
|
Sub Par posted:You can find out more by adding "on error goto errReport" on the line after "sub addhyperlinks()" and then right before "end sub" add this: Yes, the sheet is password protected. I'll remove it and try again.
|
# ¿ Jun 23, 2011 05:04 |
|
OK! The revised code worked great. Now, I know that I can create a seperate macro for each sheet that I have data on, but how would modify the so that I could have a single macro to scan each sheet in the work book? Also, I played around with the code a bit and I noticed that if I give a row range with more rows than is in the "COMPANY LIST" sheet (where I want the links to be displayed) they I'll get that same "400" error. This list is going to be updated on an ongoing basis so as new names are added to the COMPANY LIST sheet and the correspoding data is put on the related sheet the macro will need to be run to update all the links.
|
# ¿ Jun 26, 2011 06:04 |
|
I have an Excel workbook that contains data on multiple financial transactions (basically put and call options). Each row is a seperate transaction and each transaction spans about 12 columns. I have created several workbooks for other employees that use the exact same data that I have but then there are additional calculations and formula applied to the data. The problem is that as financial transactions are removed or new ones are added I have to go into all the other workbooks and manually update them. I would like to be able to just update my workbook and then have the other automatically do the same (i.e. adding or removing rows as necessary). Is this possible?
|
# ¿ May 13, 2012 04:16 |
|
G-Dub posted:This sounds like a job for a database. Is Access an option?
|
# ¿ May 17, 2012 16:43 |
|
|
# ¿ Apr 28, 2024 00:44 |
|
jusion posted:Is Google Docs an option?
|
# ¿ May 18, 2012 14:35 |