Index/Match VBA

[mulholm]

ActiveCell.FormulaR1C1 = _
"=INDEX('Department Adherence'!R[-16]:R[1048559],MATCH(""Department Total"",'Department Adherence'!C[-1],0),3)"

Is there a way to amend this formula so that it actions the formula in column "C" only if column "B" equals "Department Stats"

If column "B" equals anything else it doesnt do anything?

 

[Worf]

Something like this:

Sub mulholm()
Dim s$
s = "Department Adherence"
If ActiveCell.Column = 3 And Cells(ActiveCell.Row, 2) = "Department Stats" Then _
ActiveCell.FormulaR1C1 = _
"=INDEX('" & s & "'!R[-16]:R[1048559],MATCH(""Department Total"",'" & s & "'!C[-1],0),3)"
End Sub

 

[mulholm]

Doesn't seem to be working.

The word "Department Total" will appear in column "B" all the time but what i want is to insert the formula in to column "C" alongside.

What i need it to do is whenever column "B" equals "Department Total" then the formula is actioned in column "C".

 

[mulholm]

Also the word "Department Total" will not be a constant row number as this may change depending on who I have in.

 

[JackDanIce]

Try:

Sub AddForm()
    
    Dim LR  As Long
    LR = Cells(Rows.Count, 2).End(xlUp).Row
    
    Cells(2, 3).Resize(LR - 1).FormulaR1C1 = "=IF(RC[-1]=""Department Stats"",=INDEX('Department Adherence'!R[-16]:R[1048559],MATCH(""Department Total"",'Department Adherence'!C[-1],0),3),"")"
    
End Sub

 

[mulholm]

No joy

I managed to get worf's code to work however the issue i have is that the word "Department Total" will change row number most days depending on staff numbers.
The "activecell.column = 3" works when i am clicked on the cell next to the word however i want to code to work automatically when column "B" equals "Department Total"
Hope this makes sense.

 

[Worf]

Maybe event code:

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s$, t$
s = "Department Adherence"
t = "Department Total"
If Target.Column = 2 Then
    Select Case Target
        Case t
            Target.Offset(, 1).FormulaR1C1 = _
            "=INDEX('" & s & "'!R[-16]:R[1048559],MATCH(""" & t & """,'" & s & "'!C[-1],0),3)"
    End Select
End If
End Sub

 

[mulholm]

Ok,

that works great.
Only issue i have noticed is in the "R[-16] section works when "Department Total" is in column B and row 17
however if the next day Department Total is in Column B, row 16 then it doesnt work as the code should read R[-15]
is there a way to get around this?

 

[mulholm]

I managed to get it by changing R[-16] to C[] instead. thanks for all your help.