Excel 2010 - Auto populate cells

[Darkbatman]

below is sheet No1 & 2:

Sheet 2:

i would like to Automate excel so that it takes the inspection number from Sheet 1 (yellow cells) and put it in the appropriate location in sheet 2 (Red Cells).






For example: 1st yellow cell in sheet 1 is "Floor paint" in the "Entrance". now it should look for the cell in sheet 2 that falls in the intersection between "Floor paint" Column & "Entrance" row.


How can this be done?


Thanks in advance.

 

[Kenneth Hobson]

I would use a VBA macro. The question I have is do you want dynamic automation for Sheet1!D by sheet change event, or static automation where all are filled in a single run, or semi-automation using a formula like a custom lookup, or use existing formulas?

 

[Darkbatman]

I would use a VBA macro. The question I have is do you want dynamic automation for Sheet1!D by sheet change event, or static automation where all are filled in a single run, or semi-automation using a formula like a custom lookup, or use existing formulas?

Unfortunately i'm a very basic user, so i don't really know if i should dynamic and static, but i'll be adding the information manually in sheet 1, and then some magic will be done so that it transfers automatically in the appropriate cell in sheet 2.

 

[Kenneth Hobson]

Copy this into a Module. Enter the UDF formula as commented into your Sheet2!C3 drag and fill. The Test Sub is just a quick test. It is not needed. And, welcome to the forum!

Sub test_iMatrix()
  MsgBox iMatrix(Range("Matrix1"), "Floor Paint", "Backyard")
End Sub

'=imatrix(Matrix1,C$2,$B3)
Function iMatrix(rMatrix As Range, sCol1 As String, _
  sCol2 As String, Optional Col1 As Integer = 1, _
  Optional col2 As Integer = 2, Optional col3 As Integer = 3) As Variant
  
  Dim i As Long, r As Range
  
  For i = 1 To rMatrix.Rows.Count
    If rMatrix(i, Col1).Value = sCol1 And rMatrix(i, col2).Value = sCol2 Then Exit For
  Next i
  
  If i > rMatrix.Rows.Count Then Exit Function
  iMatrix = rMatrix(i, col3).Value
End Function