This can't be done with formulas. I have provide a short VBA sub to do this, which runs when you press the button at the top of the Photos column.
You didn't give any information about your file naming convention, so I assumed that the photo files use a ".JPG" extension. If this is wrong you can modify the code to suit. (If you are unfamiliar with VBA, you can go to the Developer tab on the ribbon, click Design Mode, then double-click on the Refresh Links button. This will take you to a screen where you can see the code. See the place where it says ".JPG"? Change that to whatever you use.)
An alternative would be to implement the Worksheet_Change function and update links whenever one is changed. That has some advantages but I got the sense this is a one-time setup and you would need to process data already in place, so I went with the button.
P.S. I happen to be a "sir" but we're not all sirs!
Bookmarks