Forum Discussion

ColinK's avatar
ColinK
Copper Contributor
May 06, 2023

Formula problem in Excel

I run a national photographic competition and am looking for a way to insert the search function used in windows explorer *-(entrantid)-* into my excel sheet. My main search formula is =hyperlink\fo...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 08, 2023

ColinK 

Here is an example of how you could do this:

 

=HYPERLINK("C:\folder\subfolder\*-" & A1 & "-*\","View Images")

 

 

  • ColinK's avatar
    ColinK
    Copper Contributor
    May 08, 2023

    NikolinoDE, thank you for your answer, unfortunately this doesn't open the files. After inserting my file structure this is what i end up with.
    =HYPERLINK("\NDPS UK Salon\NDPS UK Salon 2023\Entries\Image Bank\*-" &[@entrantid] & "-*\","View Images")
    I keep all the images in an image bank on a drive named NDPS UK Salon and each entrant can enter up to 12 images.
    The excel file is titled as -
         A1             A2            etc.
    entrantid username
    1001       fredbloggs
    When i put the formula in I am not getting an error, but on clicking on 'View Image' I get 'Cannot Open the Specified File'
    In the image bank, the images are title DI-CRTV-1005-00808.jpg where 1005 is the entrantid.
    I have typed in *-1005-* in the search box in explorer and this has the required result showing me all the entrants images at once, but i cannot adapt this for excel.
    ColinK

Resources