Count to produce a list if dates.

I have been fighting this issue for a whie and decided to 'go to the matresses'.

I am trying to create a list of dates in various tables but have run into an issue with my formula.

As I add new dates into the table the formula will not properly update. I have been manually recopying the formula and at the same time trying to refine the formula. The formula in question is as follows and the you can see in the example how I am trying to use it.

Please Login or Register  to view this content.

Jim O

How about instead in L2

=SMALL(Table1[Date], K2)

... and delete col D.

Or simplify the formula in D3 to

=N(D2) + (C3<>C2)

... change L2 to

=INDEX(Table1[Date], MATCH(K2,Table1[Count]))

Row\Col	C	D	E	F	G	H	I	J	K	L	M	N
1	Example 1							Goal	8		Dates
2	Date	Count								1	04/30/2015	M2: =SMALL(Table1[Date], L2)
3	04/30/2015	1		D3: =N(D2) + (C3<>C2)						2	05/01/2015
4	05/01/2015	2								3	05/01/2015
5	05/01/2015	2								4	05/01/2015
6	05/01/2015	2								5	05/02/2015
7	05/02/2015	3								6	05/02/2015
8	05/02/2015	3								7	05/02/2015
9	05/02/2015	3								8	05/03/2015
10	05/03/2015	4									#VALUE!
11	05/03/2015	4
12	05/03/2015	4
13	05/03/2015	4
14	05/04/2015	5
15	05/04/2015	5
16	05/05/2015	6
17	05/05/2015	6
18	05/05/2015	6
19	05/06/2015	7
20	05/07/2015	8
21	05/07/2015	8

Or lose the uneeded column of the table:

Row\Col

C

D

E

F

G

H

I

J

K

L

M

N

1

Example 1

8

Dates

L1: =SUMPRODUCT(1 / COUNTIF(Table1[Date], Table1[Date]))

2

Date

Column1

1

04/30/2015

M2: =SMALL(Table1[Date], L2)

3

04/30/2015

D3: Blank

2

05/01/2015

4

05/01/2015

3

05/01/2015

5

05/01/2015

4

05/01/2015

6

05/01/2015

5

05/02/2015

7

05/02/2015

6

05/02/2015

8

05/02/2015

7

05/02/2015

9

05/02/2015

8

05/03/2015

10

05/03/2015

#VALUE!

11

05/03/2015

12

05/03/2015

13

05/03/2015

14

05/04/2015

15

05/04/2015

16

05/05/2015

17

05/05/2015

18

05/05/2015

19

05/06/2015

20

05/07/2015

21

05/07/2015

You're welcome. But you absolutely don't need the last argument in

=INDEX(Table1[Date], MATCH(K2,Table1[Count], 0))

It forces a linear search, which will become very slow as the list of dates grows long.