I've looked through a lot of other questions that are quite close to what I'm trying to achieve but aren't really the true outcome I'm looking for so my apologies in advance if this seems too close to a duplicate question.
Situation
I've got a Excel workbook with multiple sheets in. One of these sheets is functioning as a master 'search' function which is very easy to use for an end-user.
End Goal
What I'm trying to do is have a VBA macro that when an end-user inputs value into "B3" for example and hits a command button, the script would take that value from whatever is in B3 and try to locate that value(match) within column A on a hidden sheet called 'locationData' once it's found a match return whatever is in column B from 'locationData' (which happens to be a hyperlink to a file). There can be multiple results however so it would have to continue the check through to the end.
So for example here is a set of data that you would find on locationData
| A | B
|------|------------
| 1224 | ..//path/1 |
| 1224 | ..//path/2 |
| 1464 | ..//path/ |
In this above example I'd need a user that when they are on the Overview sheet be able to put 1224 into cell B3 and then press a commandbutton (Search or Go or similar) and then it would return the ..//path/1 and ..//path/2 into B4 and B5 respectively. Of course if it found more it would continue down or just stop here.
I've tried various things but am back at the drawing board now so I'm willing to try anything here. Let me know what you think might be the best approach and regardless of whether or not I've tried it already I'll give it another go and update.
Thanks in advance! I'd attach an example worksheet here but unfortunately I don't know how and I didn't want to upload to a random third party and include the link in case that was against the rules.
Situation
I've got a Excel workbook with multiple sheets in. One of these sheets is functioning as a master 'search' function which is very easy to use for an end-user.
End Goal
What I'm trying to do is have a VBA macro that when an end-user inputs value into "B3" for example and hits a command button, the script would take that value from whatever is in B3 and try to locate that value(match) within column A on a hidden sheet called 'locationData' once it's found a match return whatever is in column B from 'locationData' (which happens to be a hyperlink to a file). There can be multiple results however so it would have to continue the check through to the end.
So for example here is a set of data that you would find on locationData
| A | B
|------|------------
| 1224 | ..//path/1 |
| 1224 | ..//path/2 |
| 1464 | ..//path/ |
In this above example I'd need a user that when they are on the Overview sheet be able to put 1224 into cell B3 and then press a commandbutton (Search or Go or similar) and then it would return the ..//path/1 and ..//path/2 into B4 and B5 respectively. Of course if it found more it would continue down or just stop here.
I've tried various things but am back at the drawing board now so I'm willing to try anything here. Let me know what you think might be the best approach and regardless of whether or not I've tried it already I'll give it another go and update.
Thanks in advance! I'd attach an example worksheet here but unfortunately I don't know how and I didn't want to upload to a random third party and include the link in case that was against the rules.