How to go to a Cell Address which is the result of a formula

[JoeMichelJarre]

Hi folks,

I have a spreadsheet of company names and Registration numbers which is in alphabetical order and runs to several thousand rows. In order to match a name to a number I have a cell where a user can type in the first few letters of a company name. Using VLOOKUP this gives you the Company Number just beneath. In the next cell beneath, I am using the CELL and MATCH functions to give me the cell address of the company, which gives me an absolute reference. So you will have something like this in the top couple of rows

ABC Ltd
T0029594T
$B$128


Now, I would like to go to this cell location onscreen. I know I can't jump direct to this cell without using VBA which isn't my forté, so I am wondering if there is any way I could use the HYPERLINK function? My less than ideal solution would be for the user to click on the cell address given and the sheet moves to that row or cell or whatever. Or for a new hyperlink to be created to the right of this cell using the given cell reference as the link, or something along these lines.

I know you could press F5 and tyoe in the given cell reference but I would prefer a simpler option as I am dealing with a huge list of potential users with verying degress of Excel experience.

Hoping there is a solution anyway!

Thanks

Joe

[Syrkrasi]

Try This

=IF(A2="","",HYPERLINK("[Book1.xlsx]sheet1!"&A2,"Hyperlinking to Cell "&SUBSTITUTE(A2,"$","") ))

See Attachment

[JoeMichelJarre]

Once I figured out the workbook reference it worked like magic. Cheers! Now I'll spend the rest of the day trying to figure out why it works! Lol

Thanks again.

[Syrkrasi]

I'm glad that I was able to assist you. If this completes your request, please mark the thread as Solved. Thanks

[pheld3423]

Is there a way to jump to a cell in this way. Column a3:a160 hold numbers that correspond to lot numbers. I would like to input the lot number and then jump to the corresponding cell. Is that possible? And if it is, could you please give me a formula for that. I NEED HELP, pulling out the last few hairs I have.

[protonLeah]

@pheld3423,

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

This is your second one today with the same question