Hyperlink to a Value

[_Ryan_]

Hi everyone, I have done a search and found some threads about this, but everything I tried doesnt work, so I figured I'd create a thread to my specific situation.

What I am trying to do is have a hyperlink go to another sheet after searching the second sheet for the same value. I have a sheet labeled "IQI" which has the total number of cases for a certain condition. I have another sheet labeled "IQI Cases" which lists out each case individually.

I want to be able to click on the Condidion in the IQI sheet and have that take me to that specific spot in the IQI Cases sheet where the cases for that condition begin. Right now I just have a simple hyperlink to a range of cells. But each month a report is generated and the range will be different and I have to go and change the range each time. So I guess I want a hyperlink and a lookup function combination.

I dont know if this would be a Macro or a formula.

Thanks in advance!

[JBeaucaire]

Post up your sample workbook so we can see the results desired. Point out where you're hyperlinking and where you'd like to jump to.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[_Ryan_]

OK, here is an example, I had to remove the data because of HIPAA (I work in a hospital). I just left the conditions I am referring to. So in IQI I want the name or somehow there to be a hyperlink that would take me to the spot in IQI Cases where that conditions cases begin.

So for example, if I click on Indicator 5 "Coronary artery bypass graft (CABG) volume" I want it to look up in sheet IQI Cases where the first time "Coronary artery bypass graft (CABG) volume" comes up, so that the person who is viewing the report can see the individual cases for each condition, each month, just by clicking on the condition in the IQI sheet.

Hope that makes more sense.

[jj72uk]

Maybe something along these lines?

Instead of searching for the text string search for the indicator number using a macro

Please Login or Register  to view this content.

Link the macro to a button and once clicked a prompted box appears, the user enters the indicator number...and.. there we go

[_Ryan_]

^ That is great! Is there anyway to make it into a hyperlink? So when they click the Indicator number it goes to that indicator number on another page? If not, this works perfect.

I have a few more sheets that I would like to use this code for if I cant get a hyperlink...would I just change the part where it says "With Sheets ("IQI Cases") to the sheet I want it to look in?

[DonkeyOte]

I'm not sure you're really going to need VBA here - you could perhaps just use the HYPERLINK function ?

Obviously there's no file available any longer but based on the prior VBA

Please Login or Register  to view this content.

where A1 contains the value to be searched for within Column B on IQI Cases sheet.

(that said of course depending on how many sheets you need to "iterate" if not found VBA may be a reasonable approach)

[_Ryan_]

^ thats perfect. Exactly what I was looking for. I took down the file cuz I didnt want to leave it up too long due to HIPAA (even though I took out specific patient information).

When I paste the formula into the first indicator number cell (B3) I get "#REF" When I paste it into an empty cell, I get the correct hyperlink.

Here is the file again...sorry.

[DonkeyOte]

Ah, you wouldn't be able to put the link over the value in B3 unless you made the value to be found a constant in the formula, eg:

Please Login or Register  to view this content.

with that in red being modified in each row - eg B4 uses 2, B5 uses 4 etc...


If however you were to insert a new column before the current C column you could subsequently hide column B and in the new blank column use:

Please Login or Register  to view this content.

ie the above would link to the hidden column to est. the Indicator Number to be found.

If you prefer to avoid VBA the 2nd option is a good one IMO.

[_Ryan_]

I tried pasting the second formula into column C, but after its pasted and I click it, it says "reference is not valid"

When I use the first formula in the above post that works fine. Whats the difference between the two? is one better than the other?

[DonkeyOte]

To avoid confusion see attached:

[_Ryan_]

I was using the first formula to paste the indicator number over the value in column B. Is that any different from what you did by hiding the column and putting the link in column C? Whats the benefit to doing it that way?

One of these ways is what I will use!! Thank you so much. You've been a huge help, I really appreciate it. I do have another question about modifying a Macro that has already been created by a colleague. I need it to do something differently. Is that something you can help me with via PM or should I create a new thread for that?

[DonkeyOte]

The advantage of the 2nd approach is that it's quicker to modify the links - ie you don't need to go into the formula and change the references etc... the values in B can of course themselves become results of other formulae etc etc...

Regards your Macro question - new thread please

[_Ryan_]

How did you make column B collapse?

[_Ryan_]

Nevermind, I got it! Thanks again for your help!!