Data Validation: Using hyperlinks to fill in cells

  • Hi all,

    I am looking for a way to have a list of part numbers have a "button" style approach to a more detailed version of each part - to include descriptions, inclusions etc.

    I have been trying to get a hyperlink to work, but I cant seem to find a way to get the hyperlink to pass on the data in the cell to another worksheet so I can use this to source the other relevant bits n pieces.

    Can this be done or does someone have an alternative method?


  • Hi Dave, Ritchie et al,

    I'm trying to reply to a Q from "flemmo" on Hey! That's Cool! entitled "Data Validation: Using hyperlinks to fill in cells", but I don't have permission to post on that forum! :(

    Here's my reply and attachment:

    Try the attached xls. If you click in cell B2 you'll see a Drop-Down list of Part Nos; click one of them and the associated details will be displayed in cells B4 and B5.

    The DropDown is a Data Validation by List based on the named range "Part_Nos" from cells H2:H4.

    The formulas in B4 and B5 use VLOOKUP to extract the other data from the named range "DataList" in H2:J4.

    Hope that's close to what you're trying to do!




    "Varium et mutabile semper Excel"

  • Hi Relman

    I have moved the Thread to Excel/VBA so you can answer it. I am also merging this Thread with the original to keep things tidy.

    Thanks for your vigilance

  • Hi flemmo,

    Not using hyperlinks, but double-click on the part number on sheet1 will take you to the same part number on sheet 2 showing the details and double click on part number on sheet2 will take you back to the same part number on sheet1



  • Hi guys,

    Thanks for your replies so far and apologies for posting in the wrong place.

    I have included a (cut down) version of the file I am working with.

    What I am trying to acchieve is a mechanism for clicking on the part number in the sheet "main" which then copies the data in that part number cell to cell A7 in the sheet called "item details" so that the rest of the sheet can gather the rest of the details.

    I want to stay away from anyone having access to the sheet called pricelist as it is very ugly and not user friendly. I have set up an incremental counter to use the VLOOKUP to retrieve the data from this sheet so there really is no way I can just reference somthing that is already there.

    Any ideas?

  • Hi flemmo,

    I added a double-click event macro to the main sheet (view by right clicking on the sheet tab and select view code)

    If you double click on any part number, that number will be copied to A7 on the items sheet and the sheet activated.

    (I deleted everything after row 26 on the main sheet just to make the file smaller to transfer - you'll have to copy the macro to your workbook)

    Hope this helps


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!