Auto-Created Expanded Table based on Years

[qlander]

Hi there,

Using Excel 2016 (no macro's), is it possible to create a table that automatically (via formula or PQ) expands based on starting a year?

For example, if I have a sheet that has a list of names and the starting year of each person, is it possible to create a new table that has each name for each year from the starting year to this year?

 

[Jeffrey Mahoney]

Ok, this took me a long while and many iterations. It may not be the most efficient, but it works.

qLander 20230905.xlsm
	A	B	C	D	E	F	G
1	Input Table				Result Table
2	Person	Start Year	Year Count		Person	Year	Row Index
3	James	2020	4		James	2020	1
4	Andy	2022	2		James	2021	1
5	Fred	2016	8		James	2022	1
6	Helen	2018	6		James	2023	1
7					Andy	2022	2
8					Andy	2023	2
9					Fred	2016	3
10					Fred	2017	3
11					Fred	2018	3
12					Fred	2019	3
13					Fred	2020	3
14					Fred	2021	3
15					Fred	2022	3
16					Fred	2023	3
17					Helen	2018	4
18					Helen	2019	4
19					Helen	2020	4
20					Helen	2021	4
21					Helen	2022	4
22					Helen	2023	4
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Sheet1

Cell Formulas
Range	Formula
E3:E36	E3	=IF(OR([@[Row Index]]=0,[@[Row Index]]>COUNTA(InputTbl[Person])),"",INDEX(InputTbl[Person],[@[Row Index]]))
F3:F36	F3	=IFERROR(IF(ISNUMBER([@[Row Index]]),INDEX(InputTbl[[Person]:[Start Year]],[@[Row Index]],2)+COUNTIF(E$2:E2,[@Person]),""),"")
G3:G32	G3	=IFERROR(IF(COUNTIF(G$2:G2,MAX(G2,1))<INDEX(InputTbl,MAX(G2,1),3),MAX(G2,1),IF(COUNT(G$2:G2)>=SUM(InputTbl[Year Count]),"",MAX(G2,1)+1)),"")
C3:C6	C3	=YEAR(NOW())-[@[Start Year]]+1