How to show certain data on click on a line?

Question

excel

I have Excel 2007, if the task is not solved on it, I can install another version.
An implementation on Google Sheets is also suitable.
There are 3 sheets: Managers, Clients and Orders.
How to make the data change at the top when clicking on a line with products.
The data is linked by client id and manager id.
On the last picture, the click was on the "Meat" cell, the data should be on top as shown in the picture.

Sheets Managers and Clients

Updt. Through the "Source code" added a script of the form:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Range("D5:E9"), Target) Is Nothing Then Exit Sub
    Range("B1").Value = "=ВПР(B" & ActiveCell.Row & ";клиенты!A:C;2;0)"
    Range("C1").Value = "=ВПР(B" & ActiveCell.Row & ";клиенты!A:C;3;0)"
    Range("B2").Value = "=ВПР(С" & ActiveCell.Row & ";менеджеры!A:C;2;0)"
    Range("C2").Value = "=ВПР(С" & ActiveCell.Row & ";менеджеры!A:C;3;0)"
End Sub

I get error 1004 on event.
Although the formula works fine without the macro:
=ВПР(B5;клиенты!A:C;2;0)
I can't figure out what's wrong?
The VLOOKUP function cannot work through a macro? What then to replace it?

Answer 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Range("D5:E9"), Target) Is Nothing Then Exit Sub
    Range("B1").FormulaLocal = "=ВПР(B" & ActiveCell.Row & ";клиенты!A:C;2;0)"
    Range("C1").FormulaLocal = "=ВПР(B" & ActiveCell.Row & ";клиенты!A:C;3;0)"
    Range("B2").FormulaLocal = "=ВПР(C" & ActiveCell.Row & ";менеджеры!A:C;2;0)"
    Range("C2").FormulaLocal = "=ВПР(C" & ActiveCell.Row & ";менеджеры!A:C;3;0)"
End Sub

Answer 2

You need to write macros in vba. By changing the cell on the SelectionChange event https
://stackoverflow.com/questions/23473721/execu...
each time the cursor is moved, it is checked against the condition\holes, and the necessary actions are performed.
But generally it is a perversion, for this purpose Access is.

Answer 3

For Google sheets there is onSelectionChange (event).

1. Get cell address and content
   let value = range.getCell(1, 1).getValue()
   
2. Search data for this query
   data.filter(row=>row[0]===value)
   
3. And write the result in the desired cells
   range.setValues(outData)
   

Or you can select one cell for the current selection value, and look for values ​​using formulas with VLOOKUP / FILTER, such a hybrid method will work faster and is more resistant to editing the table structure.
If you need a ready-made solution - write to PM