We can do this using VBA.
To open the VBA Editor, you hit Alt+F11 with your spreadsheet open:
Now we want this code to run when the cell changes, so we select our sheet in the VBA editor by double clicking on it
Then we choose Worksheet and Change from the two dropdowns:
This creates a function that will run whenever any cell on the worksheet changes.
We want our code to run when cell B3 changes, so we start by making sure that it did.
We can do this like so:
If Not Intersect(Target,[B3]) Is Nothing Then
Target is whatever happened to change - it can be one cell, or many cells.
By using Intersect, we just check to see if B3 was one of the cells that changed.
To insert an image we can use this code:
With ActiveSheet.Pictures.Insert(URL)
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 75
.Height = 100
End With
.Left = Cells(1, 2).Left
.Top = Cells(1, 2).Top
.Placement = 1
.PrintObject = True
End With
In the above code, "URL" refers to the path to the image.
If the images are in the same directory as the spreadsheet, we can use ThisWorkbook.Path & "\" & [B3] & ".jpg"
This will take the workbook path, add the necessary \ to the end of it, then combine B3 with .jpg.
In your example image, this would become C:\WhateverYourPathIs\BS-01-02-03-A-01.jpg
Moving through the rest of the code, LockAspectRatio ensures that the image doesn't stretch and resize weirdly to fit whatever the dimensions are. We may decide that it's okay for the image to stretch to fill up all the remaining space if we so desire, and we can set this to msoFalse (which is 0)
Using .Left and .Top, we can set the top-left corner position of the image.
Using .Width and .Height, we can change these dimensions as well.
As for the requirement that it "fill the remaining space", it may be necessary to determine what that space is (and whether or not it will change).
It may also be necessary to choose whether or not we want to stretch it out to fill the space.
So, to put it all together and give you an idea of how this can all be done:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B3]) Is Nothing Then
With ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & [B3] & ".png")
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 500
'.Height = 500 'Not necessary when locking aspect ratio
End With
.Left = [B3].Left
.Top = [B3].Top + [B3].Height
.Placement = 1
.PrintObject = True
End With
End If
End Sub
Keep in mind that B3 in this case is changed by a formula that appears to be altered on a different sheet, so make sure that you capture the change where it needs to be (the worksheet change event only fires for manual user changes).
Results (after changing cell B3 to sohelp1 with the image you linked saved in the same directory as my spreadsheet):
A few final notes:
You'll probably want to remove the image should the cell change again.
If there's only one image on the sheet, you can add the line ActiveSheet.Pictures.Delete to the code before you insert a new picture.
You'll also want to make sure that if the actual changes are happening on a different sheet that you reference your sheets accordingly. This just means changing ActiveSheet to whatever sheet you want these changes to occur on (and skipping the shorthand [B3] type cell references - you can use Sheet1.Range("B3") instead to refer to the cell on Sheet1 (as an example)).
If you have any questions, feel free to let me know.
