Dynamic list with Offset and indirect

Try this to create your Dynamic Named Range:

In the instance, the DNR, nrList is defined as:

Formula:

=Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(MATCH(REPT("z",26),Sheet2!$A:$A,1),MATCH(9.999+35,Sheet2!$A:$A,1)))

	A	B	C	D	E
1	List (with spaces)
2	a		f	=INDEX($A:$A,MATCH(REPT("z",26),$A:$A,1))
3	b		3	=INDEX($A:$A,MATCH(9.999+35,$A:$A,1))
4	c		f	=INDEX($A:$A,MAX(MATCH(REPT("z",26),$A:$A,1),MATCH(9.999+35,$A:$A,1)))
5			nrList	=Sheet2!$A$2:INDEX(Sheet2!$A:$A,MAX(MATCH(REPT("z",26),Sheet2!$A:$A,1),MATCH(9.999+35,Sheet2!$A:$A,1)))
6	1		6	=SUM(nrList)
7	2
8	3
9
10	d
11	e
12	f
13

Regards, TMS

Forgot, unique entries, excluding spaces, Array Formula:

Formula:

=IFERROR(INDEX($A$2:$A$100, MATCH(0, IF(ISBLANK($A$2:$A$100), 1, COUNTIF(B1:$B$1, $A$2:$A$100)), 0)),"")

committed with Ctrl-Shift-Enter rather than just Enter