Using a named value to reference a table

[gberg]

I'm trying to use a named value to reference a table

The "Named Value" = PM_JPR_TBL
In this example I want the value to be "TBL_Dec_JPR_PY"

I want to use PM_JPR_TBL in these two locations but I can't make it work. Any help would be appreciated

    JPR_tab = ActiveSheet.Name
    If JPR_tab = "Jan JPR" Then
        PM_tab = "Dec JPR (PY)"
    End If
    If JPR_tab = "Jan JPR" Then
        PM_JPR_TBL = "TBL_Dec_JPR_PY"
    End If
    'Get JPR Table Name
    Range("A2").Select
    Dim TableNameJPR As ListObject
    Set TableNameJPR = ActiveCell.ListObject
    
    
    Range(TableNameJPR & "[ACCEPTED LABOR]").Value = "=IF(IFERROR(TEXTAFTER([@[PROJ '#]],"".""),"""")=""PM"",SUMIF(PM_JPR_TBL[PROJ '#],LEFT([@[PROJ '#]],7),TBL_Dec_JPR_PY[ACCEPTED LABOR]),SUMIF(TBL_Projects[Project '#],LEFT([@[PROJ '#]],7),TBL_Projects[Accepted Labor]))"

 

[Alex Blakenburg]

Because you wanting to use a variable inside of a string (your formula), you would need to pull the string apart and add in the variable using:
& PM_JPR_TBL &
Since this a convoluted process a commonly used technique is to put a place holder where you want the variable to be used and use the replace command to substitute it with the value held by the variable.
A common placeholder is "@" but that doesn't work for tables so I have used "~" for the 2 values you highlighted in yellow above.

Below are 2 options. It is often done as a single line but I find it clearer to break it up.
PS: There is too much setup required for me to test this. If you use the sFormula approach you can print this to the immediate window to see how it compares to a working formula in the spreadsheet.

    ' Option 1
    Range(TableNameJPR & "[ACCEPTED LABOR]").Formula = Replace( _
                                                        "=IF(IFERROR(TEXTAFTER([@[PROJ '#]],"".""),"""")=""PM"",SUMIF(~[PROJ '#],LEFT([@[PROJ '#]],7),~[ACCEPTED LABOR]),SUMIF(TBL_Projects[Project '#],LEFT([@[PROJ '#]],7),TBL_Projects[Accepted Labor]))" _
                                                        , "~", PM_JPR_TBL)
    ' Option 2
    Dim sFormula As String
    sFormula = "=IF(IFERROR(TEXTAFTER([@[PROJ '#]],"".""),"""")=""PM"",SUMIF(~[PROJ '#],LEFT([@[PROJ '#]],7),~[ACCEPTED LABOR]),SUMIF(TBL_Projects[Project '#],LEFT([@[PROJ '#]],7),TBL_Projects[Accepted Labor]))"
    sFormula = Replace(sFormula, "~", PM_JPR_TBL)
    Range(TableNameJPR & "[ACCEPTED LABOR]").Formula = sFormula

 

[gberg]

I got it to work with the & PM_JPR_TBL &

Thanks for your help

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.