Create Hyperlink based on other cell value if match found in folder or subfolder.

Hi,

I searched many forums but did not findanything to fulfill my needs. Hope someone helps me.

I have an excel workbook, & Worksheet name "Bill"

Search value in AL9 to AL1200 ( Some cells are blank), Hyperlink cell will be AC9 to AC1200.

The file extension is .pdf

Now,

Where Filename exists, then this file needs to search through subfolders 2018, 2019, 2020, 2021 of folder "Data" of E drive. (Like E:\Data\2020)

IF file found then AC value converted into a hyperlink. ( The fact is the AC value will be unchanged.)

example: AL9 value searched in drive E:\Data\ and subfolder 2018, 2019, 2020, 2021, If match found then AC9 value T-9 converted as a hyperlink like T-9.

Thanks in advance.

Thanks, RoyUK,

I have attached a file hyper.xlsx.

Hope it assists.

Thanks Again