Show column to left of named range when clicking hyperlink

Hi everyone! I have been trolling around this forum for months trying to teach myself VBA. Your answers to people's questions have been great, for which I thank you. But now I'm stuck on something that I think is probably pretty easy and hoping you can help.

I have a number of Cost Centers, each of which has one or more subordinate work centers. I have used the cost center name to define ranges for the appropriate work centers as shown below:

Cost Center Work Center Description Range Name
3U45P 2P3007001 Bearings 3U45P (2P3007001)
_________________________________________________________
3U46P 2P3008001 FPI 3U46P (2P300801, 2P3008002, 2P3008003, 2P3008004)
2P3008002 MPI
2P3008003 X-Ray Booth
2P3008004 X-Ray Real Time
_________________________________________________________
etc.

I have a data sheet ('V2') into which I import information from another system, including the cost center. This cost center entry triggers a data validation drop-down from which the user can pick the appropriate work center from an abbreviated list (from that Cost Center only). However, since the Work Center nomenclature isn't terribly descriptive, I decided to hyperlink the Cost Center name in 'V2' to the appropriate range in the worksheet where the information is stored ('WCs with QA'). This will allow them to read the Work Center description more quickly and easily.

In order to display the selected range at the top left when hyperlinking, I scavenged the following from the 'net:

Please Login or Register  to view this content.

What I would really like is to also display the column to the left of the range to reassure people that they are looking at the correct cost center's work centers. I have tried defining new variables using offset, trying

Please Login or Register  to view this content.

, nothing seems to work. Because I need the Work Center names left as they are for another function in the workbook, I can't add the description to the data validation. Is there a way to automatically display the data one column to the left? Should I somehow restructure my hyperlink to find the Cost Center in sheet 'WCs with QA' rather than the named range? Here is the code for the hyperlinks, which is done when the data are imported:

Please Login or Register  to view this content.

I haven't even started working on how to update the link if someone changes the Cost Center after the data are imported.... Thanks so much for any assistance you can provide, and Happy New Year!

Originally Posted by Richard Buttrey

Hi,

Any chance you could upload the workbook and explain with a narrative comment exactly what you want to do/select and what you want to happen?

Hi! I might be able to, but I don't have authorization to release it publically so I would have to sanitize it of any actual data first, which would mean renaming ranges, etc. Would a couple of screenshots help?

Thanks, will do on Friday after the holiday. Thanks!