Using search but returning the formatting/hyperlink of the original cell

[dvs]

Hi there

I have a spreadsheet which has a basic search function on the first tab. However, when it returns data (which is on the second tab) it does not include the hyperlink which appears in the original. I wondered if there was a way to alter the code to display the original cell as it appears (preferably with formatting too but this is not essential).

I've attached a demo version which may explain more clearly what I am looking for.

Many thanks.

[daffodil11]

I did it with VB.


I put this code on Sheet1:

Please Login or Register  to view this content.

And then I put this code in Module1:

Please Login or Register  to view this content.

To use Visual Basic, go into Excel, hit Alt+F11. This brings up the editor. Double-click Sheet1 and paste the first block of code there, which is a Worksheet Change event that fires off automatically when the named cell changes. It calls the second piece of code.

Go to Insert -> Module and paste the 2nd chunk of code there. You can update the references as needed in either piece of code.

The code is looking at B column of your list, checking to make sure it's not "" and then copying the relevant link to the search tab. The list clears itself at the beginning of each copy.

[FDibbins]

lookups return text/value answers, they do not (generally) return formatting of formulas/links. I suggest that you create a formula to put in the link you want

[dvs]

Thanks daffodil11. I thought I might have to use VBA and your solution worked perfectly. Thanks.

[daffodil11]

Cool beans, glad I was able to help out.