Hi all,
How to go to specific cell which appears somewhere in a column of data, say $B$10:$B$20000. The specific value of to lookout for in $B$10:$B$20000 is available in $A$1.
I want something like a hyperlink or a button placed somewhere in the top rows of the sheet which when clicked, can take me directly to the cell where the looked-up value appears in $B$10:$B$20000, instead of manually scrolling down every time there is a change in value at $A$1.
thank you
(edited)
Hello,
yes - this can be done using function:
=HYPERLINK("#$G$" & MATCH($A$1;B:B;0);"Link to match: " & $A$1)
See the following sample file:
Note(s):
CTRL+Click to follow the hyperlink.B (Update 1: 25. Jun 2021 10:21:14 - thanks to @robleyd to focus on this fact)Hope that helps.
Hi @anon73440385, I regret typo error in the question. I edited it. There is no $G$10:$G$20000. It is all $B$10:$B$20000. I request you to please re-read the problem and suggest a solution.
Did you think to try changing G to B in the solution proposed?
@robleyd, No, I didn’t. My stupid typo error in the original question presented an entirely different / wrong understanding of problem. So simply changing G to B in the suggested solution isn’t going to help.
Using the esteemed Opaque’s sample sheet, if I substitute B for G in the formula presented, the hyperlink takes me to the (first) matching value in B. From my reading of your edited question, this seems to be the desired result. Or am I misunderstanding something?
an entirely different / wrong understanding of problem
Absolutely “No” - it’s the same type of problem and from that point it is absolutely reagardless whether you want to jump to a cell in a specific row in column A,G (orig. question) or column AMJ.
=HYPERLINK("#$B$" & MATCH($A$1;B:B;0);"Link to match: " & $A$1)
(I start to understand, why there are people leaving this site as helper, due to questioners even don’t try to understand a proposed solution).
And “No - I won’t provide a new solution” which fits 100% to your (changing) needs. Understand the current one, and you’ll be able to create a new solution on similar problems.
Thanks @robleyd, I too was able to reproduce the result. But, actually, the range starts from 10th row and in the preceding rows there are some other numbers. I think this formula looks for the value in the entire column and not just a given range. This way I think there a possibility of wrong lookup in the first 9 rows itself. I did try giving the range as beginning from 10th row, but then, the link/button then takes me to a wrong cell.
I did try giving the range as beginning from 10th
The next constraint not affecting the principle of the solution. Bye - Sai.
B:B is a short way of saying ‘all of column B’. Just define the range you want to search.