How to invert this formula

[LuGyver]

Excel 2007 using Win10.
Trying to invert the sheet so the dates can be newest (top) to oldest (bottom) but the formula in columns N-R which counts the skips (blank cells) between won't cooperate.

Also, if there is a way to completely eliminate columns H4:L27 (where H2:L2 need to be anchors) that would be great, but it's not a big deal.
No VBA
TY, Hope I did XL2BB correctly

Book1
	H
2	1
Sheet1

 

[awoohaw]

Your xl2bb mini**** only has one cell provided. Please select all the range (or a representative portion) before you click on "Mini Sheet"

Thanks in advance.

 

[LuGyver]

Thank you! I thought it looked too small

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	All	3^3	2^2	1^1	0		Eliminate if possible						Counts the Skips
2								1	1	1	1	1
3
4	8/17/2023	76	78	68				1	1	1				1	1	1
5	8/18/2023	87	80	70	83	82		1	1	1	1	1		0	0	0	2	2
6	8/19/2023	72	76					1	1					0	0
7	8/20/2023	82	85	25	83	87		1	1	1	1	1		0	0	1	1	1
8	8/21/2023	44	42	42	49	49		1	1	1	1	1		0	0	0	0	0
9	8/22/2023	70	73	3				1	1	1				0	0	0
10	8/23/2023	40	44	4				1	1	1				0	0	0
11	8/24/2023	80	83					1	1					0	0
12	8/25/2023	15						1						0
13	8/26/2023
14	8/27/2023	39	35					1	1					1	2
15	8/28/2023	51	56	16				1	1	1				0	0	4
16	8/29/2023	95	90	50	94	90		1	1	1	1	1		0	0	0	7	7
17	8/30/2023	98	98					1	1					0	0
18	8/31/2023	23	24	34	27			1	1	1	1			0	0	1	1
19	9/1/2023	44	40	40	49	49		1	1	1	1	1		0	0	0	0	2
20	9/2/2023	23						1						0
21	9/3/2023	68						1						0
22	9/4/2023
23	9/5/2023	22						1						1
24	9/6/2023	65						1						0
25	9/7/2023
26	9/8/2023	28	20	80	27			1	1	1	1			1	6	6	6
27	9/9/2023	19	18	98				1	1	1				0	0	0
28
Sheet1

Cell Formulas
Range	Formula
N3:R3	N3	=IF(H3=1,IF(H3<>"",COUNTBLANK(INDEX(H2:H$16,MATCH(9.99999999999999E+307,H2:H$16)):H3),""),"")
N4:R27	N4	=IF(H4=1,IF(H4<>"",COUNTBLANK(INDEX(H$2:H3,MATCH(9.99999999999999E+307,H$2:H3)):H4),""),"")
H4:L27	H4	=IF(B4="","",1)

 

[LuGyver]

Is this possible?

 

[awoohaw]

Is this possible?

I was just trying a couple different ways to select and sort. I can't seem to get it to work either. I tried sorting the entire area, then only the first few areas where you have the data. Hopefully other forum members will pop in.

Have you considered having another section with formulas pointing to this as a data source?

 

[LuGyver]

I was just trying a couple different ways to select and sort. I can't seem to get it to work either. I tried sorting the entire area, then only the first few areas where you have the data. Hopefully other forum members will pop in.

Have you considered having another section with formulas pointing to this as a data source?

Perhaps that may work but I'm not sure how to set it up. I'm open to examples.
Thank you for trying.

 

[bebo021999]

There are 2 sheets, one for A-Z and other for Z-A. Choose which one that work.
Both use same formula. Just sort 1st table A-Z and Z-A.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	All	3^3	2^2	1^1	0		Counts the Skips
2
3
4	8/17/2023	76	78	68				1	1	1
5	8/18/2023	87	80	70	83	82		0	0	0	2	2
6	8/19/2023	72	76					0	0
7	8/20/2023	82	85	25	83	87		0	0	1	1	1
8	8/21/2023	44	42	42	49	49		0	0	0	0	0
9	8/22/2023	70	73	3				0	0	0
10	8/23/2023	40	44	4				0	0	0
11	8/24/2023	80	83					0	0
12	8/25/2023	15						0
13	8/26/2023
14	8/27/2023	39	35					1	2
15	8/28/2023	51	56	16				0	0	4
16	8/29/2023	95	90	50	94	90		0	0	0	7	7
17	8/30/2023	98	98					0	0
18	8/31/2023	23	24	34	27			0	0	1	1
19	09/01/2023	44	40	40	49	49		0	0	0	0	2
20	09/02/2023	23						0
21	09/03/2023	68						0
22	09/04/2023
23	09/05/2023	22						1
24	09/06/2023	65						0
25	09/07/2023
26	09/08/2023	28	20	80	27			1	6	6	6
27	09/09/2023	19	18	98				0	0	0
A-Z

Cell Formulas
Range	Formula
H4:L27	H4	=IF(B4="","",IF(B3<>"",0,ROW()-IFERROR(LOOKUP(2,1/(B$3:B3>0),ROW(B$3:B3)),2)-1))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	All	3^3	2^2	1^1	0		Counts the Skips
2
3
4	09-Sep	19	18	98				1	1	1
5	08-Sep	28	20	80	27			0	0	0	2
6	07-Sep
7	06-Sep	65						1
8	05-Sep	22						0
9	04-Sep
10	03-Sep	68						1
11	02-Sep	23						0
12	01-Sep	44	40	40	49	49		0	6	6	6	9
13	31-Aug	23	24	34	27			0	0	0	0
14	30-Aug	98	98					0	0
15	29-Aug	95	90	50	94	90		0	0	1	1	2
16	28-Aug	51	56	16				0	0	0
17	27-Aug	39	35					0	0
18	26-Aug
19	25-Aug	15						1
20	24-Aug	80	83					0	2
21	23-Aug	40	44	4				0	0	4
22	22-Aug	70	73	3				0	0	0
23	21-Aug	44	42	42	49	49		0	0	0	7	7
24	20-Aug	82	85	25	83	87		0	0	0	0	0
25	19-Aug	72	76					0	0
26	18-Aug	87	80	70	83	82		0	0	1	1	1
27	17-Aug	76	78	68				0	0	0
Z-A

Cell Formulas
Range	Formula
H4:L27	H4	=IF(B4="","",IF(B3<>"",0,ROW()-IFERROR(LOOKUP(2,1/(B$3:B3>0),ROW(B$3:B3)),2)-1))

 

[LuGyver]

There are 2 sheets, one for A-Z and other for Z-A. Choose which one that work.
Both use same formula. Just sort 1st table A-Z and Z-A.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	All	3^3	2^2	1^1	0		Counts the Skips
2
3
4	8/17/2023	76	78	68				1	1	1
5	8/18/2023	87	80	70	83	82		0	0	0	2	2
6	8/19/2023	72	76					0	0
7	8/20/2023	82	85	25	83	87		0	0	1	1	1
8	8/21/2023	44	42	42	49	49		0	0	0	0	0
9	8/22/2023	70	73	3				0	0	0
10	8/23/2023	40	44	4				0	0	0
11	8/24/2023	80	83					0	0
12	8/25/2023	15						0
13	8/26/2023
14	8/27/2023	39	35					1	2
15	8/28/2023	51	56	16				0	0	4
16	8/29/2023	95	90	50	94	90		0	0	0	7	7
17	8/30/2023	98	98					0	0
18	8/31/2023	23	24	34	27			0	0	1	1
19	09/01/2023	44	40	40	49	49		0	0	0	0	2
20	09/02/2023	23						0
21	09/03/2023	68						0
22	09/04/2023
23	09/05/2023	22						1
24	09/06/2023	65						0
25	09/07/2023
26	09/08/2023	28	20	80	27			1	6	6	6
27	09/09/2023	19	18	98				0	0	0
A-Z

Cell Formulas
Range	Formula
H4:L27	H4	=IF(B4="","",IF(B3<>"",0,ROW()-IFERROR(LOOKUP(2,1/(B$3:B3>0),ROW(B$3:B3)),2)-1))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	All	3^3	2^2	1^1	0		Counts the Skips
2
3
4	09-Sep	19	18	98				1	1	1
5	08-Sep	28	20	80	27			0	0	0	2
6	07-Sep
7	06-Sep	65						1
8	05-Sep	22						0
9	04-Sep
10	03-Sep	68						1
11	02-Sep	23						0
12	01-Sep	44	40	40	49	49		0	6	6	6	9
13	31-Aug	23	24	34	27			0	0	0	0
14	30-Aug	98	98					0	0
15	29-Aug	95	90	50	94	90		0	0	1	1	2
16	28-Aug	51	56	16				0	0	0
17	27-Aug	39	35					0	0
18	26-Aug
19	25-Aug	15						1
20	24-Aug	80	83					0	2
21	23-Aug	40	44	4				0	0	4
22	22-Aug	70	73	3				0	0	0
23	21-Aug	44	42	42	49	49		0	0	0	7	7
24	20-Aug	82	85	25	83	87		0	0	0	0	0
25	19-Aug	72	76					0	0
26	18-Aug	87	80	70	83	82		0	0	1	1	1
27	17-Aug	76	78	68				0	0	0
Z-A

Cell Formulas
Range	Formula
H4:L27	H4	=IF(B4="","",IF(B3<>"",0,ROW()-IFERROR(LOOKUP(2,1/(B$3:B3>0),ROW(B$3:B3)),2)-1))

Curious. The Main Sheet (a massive sheet with tons more referenced data where the snip of XL2BB is located) ...where there's a "radio button" to push rows down and then copies the formula from those rows back up for new data entry.
To comprehend, I would make two new sheets with the A-Z and Z-A then link the table of choice to the Main Sheet, but I'm just not sure how the two new sheet would update based on the main sheet's data.
If I could wrap my head around that concept it could work but for now it's messing with me. lol out of the pan into the fire so to speak