function depDrop_(range, sourceRange){
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}
function onEdit (){
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();
  
  if (aColumn == 1 && aCell.getValue() == "") {
    var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    range.clearDataValidations();
    range.clearContent();
    return;
  }
  
  if (aColumn == 1 && SpreadsheetApp.getActiveSheet()){
    var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    depDrop_(range, sourceRange);
  }
}

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngRows As Range
    Dim rngSrc As Range
    Dim arr()
     
    Application.EnableEvents = False
     
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("G30:G40")) Is Nothing Then
        PosStr = Cells.SpecialCells(xlCellTypeLastCell).Row
        Set rngSrc = Cells(1, 1).Resize(PosStr)
        rngSrc.EntireRow.Hidden = False
     
        arr = rngSrc.Value
         For r = 1 To PosStr
               If arr(r, 1) = 2 Then
                   If Not rngRows Is Nothing Then
                       Set rngRows = Union(rngRows, Rows(r))
                   Else
                        Set rngRows = Rows(r)
                    End If 'Not rngRows Is Nothing
               End If 'arr(i, 1) = 2
        Next
    End If
    End If
    If Not rngRows Is Nothing Then rngRows.EntireRow.Hidden = True
     
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
    Dim rngRows As Range
    Dim rngSrc As Range
    Dim arr()
     
    Application.EnableEvents = False
     
    PosStr = Cells.SpecialCells(xlCellTypeLastCell).Row
    Set rngSrc = Cells(1, 1).Resize(PosStr)
    rngSrc.EntireRow.Hidden = False
     
    arr = rngSrc.Value
        For r = 1 To PosStr
            If arr(r, 1) = 2 Then
                If Not rngRows Is Nothing Then
                    Set rngRows = Union(rngRows, Rows(r))
                Else
                    Set rngRows = Rows(r)
                End If 'Not rngRows Is Nothing
            End If 'arr(i, 1) = 2
        Next
     
    If Not rngRows Is Nothing Then rngRows.EntireRow.Hidden = True
     
    Application.EnableEvents = True
End Sub

Set sha = MyCell.Worksheet.Shapes.AddPicture(sAddress, TRUE, True, MyCell.Left + 1, MyCell.Top + 1, -1, -1)    With sha
        .LockAspectRatio = msoFalse
        .Height = 85
        .Width = 107
    End With

Set sha = MyCell.Worksheet.Shapes.AddPicture(sAddress, False, True, MyCell.Left + 1, MyCell.Top + 1, -1, -1)
    With sha
        .LockAspectRatio = msoFalse
        .Height = 85
        .Width = 107
    End With

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)Dim sAddress As String
Dim MyCell As Range
 
    On Error Resume Next
    Set MyCell = Target.Range
     
    ChDir ThisWorkbook.Path & "\Банк изображений\" & Target.Name
    sAddress = Application.GetOpenFilename(Title:="Выберите файл")
    MyCell.Font.ThemeColor = xlThemeColorDark1
    'MyCell.Clear
    Shapes.AddPicture _
        sAddress, False, True, MyCell.Left + 5, MyCell.Top + 5, 100, 80 'для вставки в текущее место
        Shapes.AddPicture _
        
        sAddress, False, True, MyCell.Left + 5, MyCell.Top + 5, 200, 160
        with Worksheets("Лист2")
             'тут как-то прописать строку и столбец, куда вставить изображение
        end with

                 
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)Dim sAddress As String
Dim MyCell As Range

    On Error Resume Next
    Set MyCell = Target.Range
    
    ChDir ThisWorkbook.Path & "\Банк изображений\" & Target.Name
    sAddress = Application.GetOpenFilename(Title:="Выберите файл")
    MyCell.Font.ThemeColor = xlThemeColorDark1
    'MyCell.Clear
    Shapes.AddPicture _
        sAddress, False, True, MyCell.Left + 5, MyCell.Top + 5, 100, 80
                
End Sub