Text in cell return formula

[HelJLL]

Hi All,
Really new to VBA but I'm trying to do the following.
If there is a value in a particular cell it needs to then return a formula in another cell however it needs to reference back to a name in another cell.
So in the following snip, the formula needs to return in the yellow highlighted based on the 4 in A1 however it needs to reference to the name in B1.

The code I have is;

Sub Check_Cell_is_empty_alt()
Dim cel As Range
For Each cel In Range("A1:A30")
If cel.Value = 4 Then
cel.Offset(0, 2).Formula2 = "=FILTER(Table!$3:$7,B1=Table!$2:$2)"
Else
End If
Next cel
End Sub

The issue is the yellow highlighted B1 doesn't change when I run the VBA it just stays as B1........ I hope I've explained this.
Thanks,

 

[RoryA]

Use:

 "=FILTER(Table!$3:$7,B” & cel.row & “=Table!$2:$2)"

 

[HelJLL]

Use:

 "=FILTER(Table!$3:$7,B” & cel.row & “=Table!$2:$2)"

Thanks for the response RoryA unfortunately I'm getting a #NAME? error using this.

 

[RoryA]

Looks like my iPad added smart quotes instead of normal ones. It should be:

"=FILTER(Table!$3:$7,B" & cel.row & "=Table!$2:$2)"

 

[HelJLL]

That worked perfectly thanks very much RoryA.