Hyperlink Style Search Macro

[miketurn]

I am looking for a macro (I believe it will be two macros needed) that could do the following....

Say you have a spreadsheet with a whole bunch of BOLD header titles throughout the page.
At the top of the spreadsheet is a list of all these BOLD header titles.
I would like to be able to click on an item in this list and have it run a search with no dialog boxes and find the first match it can and navigate to it.

(Cannot use a hyperlink in this situation because as rows are added and deleted, the cell locations of the headers will change)


EXAMPLE
Note: The numbering of the cells in this example are just an example, would like these macros to be dynamic (used for any columns / rows)

1 [U][B]GLOSSARY[/B][/U] (HEADER) (LIST AT TOP OF SPREADSHEET)
2 CARS
3 FRUITS
4 FURNITURE
....

10 [U][B]CARS[/B][/U] (HEADER)
11 camry
12 accord
13 mustang
14 corolla
15
16
.....
200 [U][B]FRUITS[/B][/U] (HEADER)
201 apple
202 banana
203 orange
204 grape
.....
305 [U][B]FURNITURE[/B][/U] (HEADER)
306 chair
307 table
308 dresser
309 bed

I would like to be able for example to....
1.) Select with my mouse cell number 3 = "FRUITS"

2.) By clicking on this cell, it activates a macro (much like a hyperlink) that does a search of its content, which in this case is "FRUITS".
No search dialog boxes, just find the first match.
Also in this search action, only return search match for this text value if it is BOLD.

3.) The macro will then navigate to the first BOLD "FRUITS" text it finds.
The built in Search with Excel does this navigation automatically when it finds a match, if possible would be great if when it navigates to a header (a search match) that it navigates / adjusts your screen so that the search match is at the top of your screen.


So as mentioned, I would imagine this would require two macros.
1.) A macro that allows you to run another macro by clicking on a specific cell.
Similar to this one below, but somehow more dynamic, because if you have to add new header titles in the GLOSSARY list, the cell location values will change.
https://www.mrexcel.com/forum/excel-questions/7971-how-do-i-run-macro-point-cell-selection.html

2.) A macro that performs the search and navigates to the match (as described above)


Thank You to anyone that takes the time to read this and is willing to try to solve it.

 

[miketurn]

Hey guys,

Hyperlinks do exist in 2003, I have been able to set them up before.
I have just never used them by applying them with a function within a cell.
I would go into "Insert" drop down menu and select "Hyperlink".

I tried out a very simple function based hyperlink from the page you mentioned @Eric W and it does work, just for some reason the one you offered calls up that window wanting a file to be linked?
Anyone have any idea why?

I would imagine not, if no one has mentioned it, but there isn't a way to set a specific cell to run a macro upon selecting it? It has to be through assigning a double click to all cells within a spreadsheet?

I am very interested in finding a solution with both of the methods you guys mention.

Like I said, I will test some more stuff out, but if anyone has anything else to share, please do so.
Thank You guys for all the help provided.

 

[miketurn]

Ahhhhaaaaa! @Eric W
I was messing around with the hyperlink approach you mentioned and I was able to get it to work.

=HYPERLINK("#"&CELL("address",Sheet1!A10),"CARS")

I figured that Excel would know what the sheet name was, meaning I figured when I entered your code that contains

Sheet1

I thought Excel still calls it "Sheet1" even if the tab is renamed, but in this case for some reason it didn't, so when I change the "Sheet1" in the function above to the name of my sheet tab, it worked.

Anyways, Wow! now that is cool! Updates as the cell is moved around just like you said it would
Thank You again for this and for all your help.


@question To Anyone
I would imagine this is not possible because I don't think a "function" is able to work this way, but does anyone know of a way to make this function be able to, when it navigates to the header that is hyperlinked, that the header selection will be at the top of your screen? Currently when it finds it, the header will be at the bottom of the screen.
It isn't a big deal, just the fact that this even works is amazing.
The macro that @Fluff provided is able to do this, but that is because it is a macro which has this ability, I don't think that a "function" would be able to perform this action.
Again not a big deal, I could always just select the item below it in the "glossary" list which will bring me to the bottom of the item above, but I just was wondering if anything like this was possible.

Thank You guys again for your help, and if anyone has an answer to my above quesiton, please let me know.

 

[Eric W]

I don't think this is possible outside of a macro.

 

[miketurn]

@Eric W
Thank you for keep coming back to help.
Yeah, I figured that, your hyperlink idea covers the main part though, which is the more important part
Still can't believe it works so well, it is one thing to be able to link the cell, but the fact that it automatically updates if the cell row number changes is amazing!