Reference Sheetname

[sapads01]

I need to pickup the sheetname for the formula below in cell A1 of a sheet called LookUps.

Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(Plan!C[-5],Plan!C[-9],RC[-2],Plan!C[-8],RC[-1])"

Can anyone help? I've tried using indirect but cannot get it to work.

 

[Joe4]

I think we need more explanation/detail.
Where is the sheetname value coming from?

By the way, you can condense what you have above to this (no need for unnecessary SELECT statements that just slow down the code):

Range("J2").FormulaR1C1 = "=SUMIFS(Plan!C[-5],Plan!C[-9],RC[-2],Plan!C[-8],RC[-1])"