Sumifs Using Name Manager (Looking to change more dynamic)

[RudRud]

Hello, Gurus.

Monthly dataset for months 1, months 2, months 3 ( will add a new dataset every month)

Sheet 5 is a total based on the months (Months 1,2,3) for selected column ( Amounting & Totalling)

I've been using List Manager with VBA code, but whenever the name of a column changes, I have to rename it to reflect the new name (for example, if an end user writes Amounted, I must change it to Amounting).

I'm seeking for other references that are more convenience and more practical.

P.S. : users sometime put the column into row 2 - 5 and i'm unsure how to write in formula (both formula / VBA code will do)

Month1:

Book2
	A	B	C	D
1	Naming	Col	Amounting	Totalling
2	Rud	1	2	3
3	An	2	2	2
4	Rud	2	3	5
5	An	2	2	2
Month1

Month2:

Book2
	A	B	C	D
1	Totalling	Col	Naming	Amounting
2	3	1	Rud	2
3	2	2	An	2
4	5	2	Rud	3
5	2	2	An	2
Month2

Month3:

Book2
	A	B	C	D
1	Col	Amounting	Totalling	Naming
2	1	2	3	Rud
3	2	2	2	An
4	2	3	5	Rud
5	2	2	2	An
Month3

Dict:

Book2
	A	B	C	D	E	F	G
1	Amounting	amou		Month1	1	amou1	=Month1!$C:$C
2	Totalling	tota		Month2	2	amou2	=Month2!$D:$D
3	Naming	name		Month3	3	amou3	=Month3!$B:$B
4						name1	=Month1!$A:$A
5						name2	=Month2!$C:$C
6						name3	=Month3!$D:$D
7						tota1	=Month1!$D:$D
8						tota2	=Month2!$A:$A
9						tota3	=Month3!$C:$C
10
11
12
13
dict

You may run the namemng, listname code to appear Column F & G

Total

Book2
	A	B	C	D	E	F	G
1		Amounting	Amounting	Amounting	Totalling	Totalling	Totalling
2	Name	Month1	Month2	Month3	Month1	Month2	Month3
3	Rud	5	5	5	8	8	8
4	An	4	4	4	4	4	4
Sheet5

Cell Formulas
Range	Formula
B3:B4	B3	=SUMIFS(amou1,name1,A3)
C3:C4	C3	=SUMIFS(amou2,name2,A3)
D3:D4	D3	=SUMIFS(amou3,name3,A3)
E3:E4	E3	=SUMIFS(tota1,name1,A3)
F3:F4	F3	=SUMIFS(tota2,name2,A3)
G3:G4	G3	=SUMIFS(tota3,name3,A3)

Named Ranges
Name	Refers To	Cells
amou1	=Month1!$C:$C	B3:B4
amou2	=Month2!$D:$D	C3:C4
amou3	=Month3!$B:$B	D3:D4
name1	=Month1!$A:$A	E3:E4, B3:B4
name2	=Month2!$C:$C	F3:F4, C3:C4
name3	=Month3!$D:$D	G3:G4, D3:D4
tota1	=Month1!$D:$D	E3:E4
tota2	=Month2!$A:$A	F3:F4
tota3	=Month3!$C:$C	G3:G4

Sub namemngbook2()
Dim i%, S%
Dim bArr()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next

bArr = ThisWorkbook.Sheets("dict").Range("a1").CurrentRegion.Value
shtname = ThisWorkbook.Sheets("dict").Range("d1").CurrentRegion.Value
'shtname = ThisWorkbook.Sheets("dict").Range("d1").Value

For S = 1 To UBound(shtname, 1)

    For i = 1 To UBound(bArr, 1)
 
        found = ThisWorkbook.Sheets(shtname(S, 1)).Cells.Find(what:=bArr(i, 1)).Address
        ActiveWorkbook.Names.Add Name:=bArr(i, 2) & shtname(S, 2), RefersToR1C1:=ThisWorkbook.Sheets(shtname(S, 1)).Range(found).EntireColumn
     
    Next i

Next S

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



End Sub
Sub DeleteNMbook2()
Dim i%, S%

Dim RangeName As Name


On Error Resume Next
For Each RangeName In Names
    ActiveWorkbook.Names(RangeName.Name).Delete
Next
On Error GoTo 0

 ThisWorkbook.Sheets("dict").Range("f1:g1500").Value2 = ""


End Sub

Sub listnamebook2()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("dict").Range("f1").ListNames
End Sub

 

[RudRud]

Hi Gurus, Still looking for references