SUM, INDIRECT and Char Formula on a Excel Table

0
votes

I have got a situation in using the SUM formula with Indirect for Table references.

I would like to use the formula in a Table(name: Consolidated_062020) and use the references of the other Table(name: IM_062020) and derive the SUM. Firstly I have recorded that and later would like to convert that into the dynamic formula as both Table names get changed every month.

The recorded formula is:

ActiveCell.FormulaR1C1 = "=SUM(INDIRECT(""IM_062020"" & ""["" &[@[Team Members]] & CHAR(10) & Consolidated_062020[[#Headers],[Volumes]] & ""]""))"

I tried the following method to convert it into a dynamic way:

Dim TableName As String
Dim TableName2 As String
Dim OLC As ListObject
Dim OLC2 As ListObject

Month = WorksheetFunction.Text(MacroSheetDate, "mm")

Year = WorksheetFunction.Text(MacroSheetDate, "yyyy")

TableName1 = "IM_" & Month & Year
TableName2 = "Consolidated_" & Month & Year

Set OLC = IndMetricsSht.ListObjects(TableName)

Set OLC2 = IndMetricsSht.ListObjects(TableName2)

I'm selecting the entire column data range in a table and giving the below-mentioned formula:

Selection.FormulaR1C1 = "=SUM(INDIRECT(" & OLC & "[" & [@[Team Members]] & Chr(10) & OLC2 & [[#Headers],[Volumes]] & "]))"

Please Help!

1
excelvbaexcel-formula
Comments are not for extended discussion; this conversation has been moved to chat. – Samuel Liew

1 Answers

1
votes

Code that solves the issue:

Selection.FormulaR1C1 = "=SUM(INDIRECT(" & Chr(34) & TableName & Chr(34) & " & ""["" & [@[Team Members]] & CHAR(10) & ""Volumes"" & ""]""))"