newbie creating lots of hyperlinks

[Dalkorian]

Please bear with me. This seems like it should be simple, but ...

I have a workbook with two sheets in it. The first sheet lists programs on one of our servers, the other lists users. I'm trying to create a hyperlink on the first sheet that will highlight (select) all users (on the second sheet) that have access to the program. (The idea is to give this to the owners of the company, so they can click on a program and see who has access to it.)

I figured out how to create named ranges on the second sheet, so I can highlight all users with a certain security code. I have a security code for each program in the first sheet in column 'C'.

My first attempt was to simply click-drag over the desired entries in the first sheet and create a hyperlink to the desired named range on the second sheet. This works fine ... as long as the first sheet isn't resorted. Sort the first sheet on another column and I discover that (apparently) the hyperlinks were created on the ROWS, not the data. In other words, the links still "work", but suddenly point to the wrong entries. Sorting by the initial column (as it was when I created the links) "fixes" it.

I've looked through the forum here and found a solution that will create the links automatically somehow (I think it was a VBScript, but I don't have the background to know), but I barely know enough about Excel to figure out what I already did; I have no clue how to create a macro or function or whatever to do this (but obviously am willing to learn!).

I would just "bite the bullet" and create the hyperlinks manually, but there are 1165 rows to go through (it would be sweet if it could cover all 4 columns I have as well, but not necessary). So I need a better answer (besides you don't learn much by biting bullets, except that lead isn't good for you).

Answer that would work: How do I fix these "row bound" hyperlinks to bind to the data in the cells, so that if I sort off of another column the links follow the data WITHOUT creating 1165 hyperlinks manually?

Best answer: How do I create a script/macro/function thingie that will create hyperlinks dynamically on a row, based on data in a column of that row (bonus kudos for allowing the links to work on ANY column, so that I could click cell A20 and have the link work based on the data in C20)? Remember that I won't get it initally, explain as you would a child (I've never created any script/macro/function thingie in Excel before; I'm a Java programmer with some WebObjects experience. I have tried to get the script thingie I saw in here to work, but after a frustrating hour all I can say is Excel is fighting me and it just won't work. I KNOW this is because I don't have a clue as to what I'm doing, which is why I'm here begging for help).

javascript:emoticon('')

Things you want to know:
Excel v.X for Mac (OS 10.3.7)
Excel experience level 0.5 (I can read the documentation - barely)

Many thanks in advance for any knowledge you can impart!

 

[PA HS Teacher]

It would be really helpful to see what these two sheets look like. Could you post an example from each sheet using Colo's HTML maker. (link at the bottom of the page)

I'm not 100% clear on what information is associated with each user on the 2nd sheet or how the names are arranged.

Have you ever used Autofilter?

Depending on how the data is arranged, you can simply choose the program name from a drop down list, and filter out all users that do not have access.

 

[Dalkorian]

It would be really helpful to see what these two sheets look like. Could you post an example from each sheet using Colo's HTML maker. (link at the bottom of the page)

Well, I tried. Was HTML Maker written for Windows? It created an HTML page that seems to show my first sheet, but trying to view the second sheet shows it trying to load 9.5 megs from somewhere, then freezes IE without displaying anything and demands a force quit (remember I'm on a Mac here). Attempting anything else from the HTML menu causes a "Compile error: Can't find project or library" error.

I'm not 100% clear on what information is associated with each user on the 2nd sheet or how the names are arranged.

Maybe I can describe it better. The first sheet (named "DAVE_PROG_CODES") has the following layout:

PROGRAM......NAME...............SECURITY......ACCESS
ACF115.........Catalog F/M.........0
SOE210.........Order Entry........1................OE
POE120.........PO Entry............2................POE
...

The second sheet (named "Sheet1", by default I think) looks like this:

CODE.....NAME....................EMPLOYEE..........SECURITY
ADB.......Andrew Belhmer......BELHMERA.........0
NRS.......Nicholas Shelton......SHELTONN.........01
CDB.......Chuck Bates...........BATESCHU.........0123456
...

As you can see, Andrew can only access Catalog F/M due to the security. Nicholas can access Order Entry as well as Catalog F/M. Chuck can access PO Entry, as well as Order Entry and Catalog F/M.

I have ranges defined in "Sheet1" to group Andrew, Nicholas and Chuck together under security '0' (the range is named "SL0"). Another range groups Nicholas and Chuck together under security '1' (named "SL1").

I'm trying to create hyperlinks so that when I click on any data in the Order Entry row, "Sheet1" is brought forth and the range for secruity '1' (SL1) is selected, which highlights Nicholas and Chuck (but not Andrew). But i have 1165 rows in "DAVE_PROG_CODES" to go through ...

Have you ever used Autofilter?

Auto-what? No, never heard of it, don't have a clue as to what that is.

Many advanced thanks again for any ideas, suggestions or clues you can offer. Hand me a Java project and I'm a diety. Question me about the computer system we're using and I know it all. Give me an Excel project and I'm simply not worthy.

javascript:emoticon('ray:')