Need formula range to automatically expand when a new row is added to table

[Mike423]

I'm currently using the below formula to sum individual payroll values from a large table into a smaller, summary table.

=IF(D2="YTD",SUMPRODUCT((G4:BQ13)*(G3:BQ3=A6)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A6)))

My question is, how can I modify this formula, so that when I drag the bottom row of the table down (it is actually a table, not just a range) to add a new row, the ranges in the formula adjust along with it to include any data entered into the new row? Picture is attached.

Thank you.

 

[KRice]

What is the name of the table? Go back to Table Design, Properties submenu and you should see the name.

 

[Mike423]

Weekly_Payroll for the entire table.

 

[KRice]

If you go to an empty column somewhere on the worksheet and type =Weekly_Payroll[ in the formula bar, do you see a list of column names appear as you type the [ character? Also, as you're typing =Weekly... you should see Excel offer suggestions for what you might want to type...you should see Weekly_Payroll appear in that list...do you? And if you select one of the column names, such as =Weekly_Payroll[Week 44] (don't forget to close the right side bracket ], what do you get? Try that for several of the columns...I'm still puzzled about what the column names are.

 

[Mike423]

Yes to all of the Yes/No questions. When I type a column name in brackets and hit enter, it returns the entire column from the table.

 

[KRice]

Okay, but if you do that for several columns within a week’s group, are you sure each has the same name (column heading)? To my knowledge, that isn’t possible because it wouldn’t be possible to distinguish between those columns based on their names. In my mockup version, when I include the week number row as my table header, Excel changes the Week 44 labels to Week 44, Week 442, Week 443, etc.

Is your table structured consistently so that the same types of hours appear for each week, and in the same order? If so, then another option is to not match the week number directly, but rather use the position of the columns within your source data array to determine where specific weeks are located.

 

[Mike423]

 

[Mike423]

Ignore the Week 45 in that last column, just an error I fixed earlier.

 

[KRice]

Without being able to explore your worksheet in detail, I can't reconcile what is happening with the column names. The approach below assumes:

1. that every week block (the group of 7 columns associated with each week) has, for the first column of each block, a column heading that might match exactly the selection in D2 (I'm not relying on column headings for the remaining columns in each week block...only the first one). A formula then extracts the 7 columns corresponding to the specified week.
2. that every week block contains the same hour types shown in A6:A12 (necessary because 7 columns are extracted for a specified week block)

Your inputs are largely limited to the first row of the formula where you'll need to confirm that the variables defined in the LET formula are pointing to the correct range references (the syntax is variable name followed by a comma followed by the range reference, and so on, with each set also separated from the preceding one by a comma). I'm not entirely satisfied with the nested LET used inside the IF function. That portion of the formula extracts the 7 columns associated with a specified week, and considering the unusual behavior you've described with the column headings, I don't think they can be trusted...and I can't reproduce a table like the one you've described (with multiple columns headings being duplicated). So the MATCH function is used to find the first match, and then we take it and the next 6 columns. Let me know if this works for your application. You should be able to extend the table down and to the right without further attention to the formula.

The helper columns at right are used by Data Validation, for the drop down list items in B2 and D2. You may already have something implemented for that.

MrExcel_20231030_B.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1			Reg Hrs	40
2	Employee:	Emp 1	Week:	Week 45		Column1	Week 44	Week 442	Week 443	Week 444	Week 445	Week 446	Week 447	Week 45	Week 458	Week 459	Week 4510	Week 4511	Week 4512	Week 4513
3						Employee Name	Regular Hours	Overtime Hours	Holiday	Sick	Vacation	Floating Holiday	Other	Regular Hours	Overtime Hours	Holiday	Sick	Vacation	Floating Holiday	Other		Helper Lists
4						Emp 1	40	2						39	2							YTD	Emp 0
5		Hours	Pay			Emp 2	32	1						33	1							Week 44	Emp 1
6	Regular Hours	20	300			Emp 3	41	4						22	4							Week 442	Emp 2
7	Overtime Hours					Emp 1	3	2						1	2							Week 443	Emp 3
8	Holiday																					Week 444
9	Sick					Emp 0	11	4						17	4							Week 445
10	Vacation	16	240																			Week 446
11	Floating Holiday																					Week 447
12	Other																					Week 45
13																						Week 458
14																						Week 459
15																						Week 4510
16																						Week 4511
17																						Week 4512
18																						Week 4513
Mike423

Cell Formulas
Range	Formula
D1	D1	=LET(HrType,A6,EmpName,B2,qryWk,D2,HrTypeLst,A6:A12,DataRng,Weekly_Payroll,HdrWkGrps,Weekly_Payroll[#Headers], EmpCol,TAKE(DataRng,,1), HdrHrType,TAKE(DataRng,1), hary, IF(qryWk="YTD", FILTER(DataRng,HdrHrType=HrType), LET(wkary,TAKE(DROP(DataRng,,MATCH(qryWk,HdrWkGrps,0)-1),,ROWS(HrTypeLst)),FILTER(wkary,TAKE(wkary,1)=HrType))), SUM(FILTER(hary,EmpCol=EmpName)))
V4:V18	V4	=LET(wklst,SORT(UNIQUE(DROP(Weekly_Payroll[#Headers],,1),,1)),VSTACK("YTD",TRANSPOSE(wklst)))
W4:W7	W4	=LET(elst,SORT(UNIQUE(TAKE(DROP(Weekly_Payroll,1),,1))),FILTER(elst,elst<>""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=$W$4#
D2	List	=$V$4#

 

[Mike423]

Payroll Template.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Schedule Sorter				YTD	YTD	YTD	YTD	YTD	YTD	YTD	YTD	YTD	YTD	YTD
2	Employee:	Employee 5	Week:	Week 44			Week 44	Week 44	Week 44	Week 44	Week 44	Week 44	Week 44	Week 45	Week 45	Week 45	Week 45	Week 45	Week 45	Week 45
3						EMPLOYEE NAME	REGULAR HOURS	OVERTIME HOURS	HOLIDAY	SICK	VACATION	FLOATING HOLIDAY	OTHER	REGULAR HOURS	OVERTIME HOURS	HOLIDAY	SICK	VACATION	FLOATING HOLIDAY	OTHER
4	Hours and Pay					Employee 1	40	2.5	0	0	0	0	0	35.2	0.8	0	4	0	0	0
5		Hours	Pay			Employee 2	32	0	8	0	0	0	0	40	0	0	0	0	0	0
6	Regular Hours	20	$ 300.00			Employee 3	40	0	0	0	0	0	0	40	0	0	0	0	0	0
7	Overtime Hours	0	$ -			Employee 4	20	0	0	8	8	0	0	28	0	0	4	8	0	0
8	Holiday	16	$ 240.00			Employee 5	20	0	16	4	0	0	0	20	0	0	0	16	4	0
9	Sick	4	$ 60.00			Employee 6	36	0	0	0	4	0	0	32	0	0	0	0	8	0
10	Vacation	0	$ -			Employee 7	43	3	0	0	0	0	0	40	0	0	0	0	0	0
11	Floating Holiday	0	$ -			Employee 8	40	0	0	0	0	0	0	47	7	0	0	0	0	0
12	Other	0	$ -			Employee 9	40	0	0	0	0	0	0	40	0	0	0	0	0	0
13	Total	40	$ 600.00			Employee 10	32	0	0	8	0	0	0	36	0	0	0	0	0	4
14
15	Time Off
16	Type	Used	Remaining
17	Holiday	16	-
18	Sick	4	-
19	Vacation	0	-
20	Floating Holiday	0	-
21	Other	0	-
Time Sheet

Cell Formulas
Range	Formula
B6	B6	=IF(D2="YTD",SUMPRODUCT((G4:BQ13)*(G3:BQ3=A6)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A6)))
C6:C12	C6	=((VLOOKUP($B$2,'Employee Details'!$C$2:$D$12,2,0))*B6)
B7	B7	=IF(D2="YTD",SUMPRODUCT((G4:BQ13)*(G3:BQ3=A7)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A7)))
B8	B8	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A8)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A8)) )
B9	B9	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A9)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A9)) )
B10	B10	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A10)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A10)) )
B11	B11	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A11)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A11)) )
B12	B12	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A12)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A12)) )
B13:C13	B13	=SUM(B6:B12)
B17	B17	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A17)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A17)) )
C17:C21	C17	=IF($D$2="YTD",VLOOKUP($B$2,'Employee Details'!$C$1:$I$12,3,0)-B17,"-")
B18	B18	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A18)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A18)) )
B19	B19	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A19)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A19)) )
B20	B20	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A20)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A20)) )
B21	B21	=IF(D2="YTD", SUMPRODUCT((G4:BQ13)*(G3:BQ3=A21)*(F4:F13=B2)),SUMPRODUCT((G4:BQ13)*(F4:F13=B2)*(G2:BQ2=D2)*(G3:BQ3=A21)) )

Named Ranges
Name	Refers To	Cells
Employee_Names	='Time Sheet'!$F$4:$F$13	B6:B12, B17:B21
Pay_Types	='Time Sheet'!$G$3:$BQ$3	B6:B12, B17:B21
Table_Data	='Time Sheet'!$G$4:$BQ$13	B6:B12, B17:B21
Weeks	='Time Sheet'!$G$2:$BQ$2	B6:B12, B17:B21

Cells with Data Validation
Cell	Allow	Criteria
B2	List	='Employee Details'!$C$2:$C$1048576
E17	List	='Employee Details'!$A$2:$A$54
D2	List	='Employee Details'!$A$2:$A$54

 

[Mike423]

Sorry, took me a minute to get the Excel add-in working, but in my previous post is the spreadsheet that should include the formulas so you are able to explore the spreadsheet in more detail. Let me know if this helps.
Thanks for all your help!