Offset to the address of the next occupied cell

[gtd526]

Hello,
Looking to Offset (or other formula) to reference the cell address of the next occupied cell moving up.
Cabinets(O:O) to 6 (N:N)
Mbath to 6
etc

No arrays please. I have to incorporate this into another formula that's not an array.
When there are multiple summary's for one invoice, I need to reference the first occupied cell address (N:N).

Thank you.

Create Invoice.xlsm
	N	O	P	Q
6	Invoice(s)	Summary		Price
7	2	Door Hinge		$100.00
8	6	Install Lighting		$500.00
9		Cabinets
10		Mbath
11	8	Walls		$280.00
12		Floor Rug
13	9	Patch Walls		$450.00
14		Thin Set Grout
15	10	Lights		$300.00
16	11	Ceiling		$50.00
17		0
18
19
20
21
22			Total:	$1,680.00
Details

Cell Formulas
Range	Formula
N15:N17,N13,N11,N7:N8	N7	=IFERROR(INDEX($A:$A, MATCH(0, IF($O$3=$C:$C, COUNTIF($N$6:$N6, $A:$A), ""), 0)),"")
O7	O7	=IFERROR(IF(COUNTIF($A$2:$A$100, $N7)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N7=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O6,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$7=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O6,0))),1))),"")
O8:O10	O8	=IFERROR(IF(COUNTIF($A$2:$A$100, $N8)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N8=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O7,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$8=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O7,0))),1))),"")
O11:O12	O11	=IFERROR(IF(COUNTIF($A$2:$A$100, $N11)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N11=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O10,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$11=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O10,0))),1))),"")
O13:O14	O13	=IFERROR(IF(COUNTIF($A$2:$A$100, $N13)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N13=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O12,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$13=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O12,0))),1))),"")
O15	O15	=IFERROR(IF(COUNTIF($A$2:$A$100, $N15)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N15=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O14,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$15=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O14,0))),1))),"")
O16	O16	=IFERROR(IF(COUNTIF($A$2:$A$100, $N16)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N16=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O15,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$16=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O15,0))),1))),"")
O17	O17	=IFERROR(IF(COUNTIF($A$2:$A$100, $N17)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N17=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O16,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$17=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O16,0))),1))),"")
O18	O18	=IFERROR(IF(COUNTIF($A$2:$A$100, $N18)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N18=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O17,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$18=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O17,0))),1))),"")
O19	O19	=IFERROR(IF(COUNTIF($A$2:$A$100, $N19)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N19=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O18,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$19=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O18,0))),1))),"")
O20	O20	=IFERROR(IF(COUNTIF($A$2:$A$100, $N20)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N20=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O19,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$20=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O19,0))),1))),"")
O21	O21	=IFERROR(IF(COUNTIF($A$2:$A$100, $N21)=1,INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N21=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O20,0))),1)),INDEX($I$3:$I$100,AGGREGATE(15,6,(ROW($I$3:$I$100)-ROW($I$3)+1)/($N$21=$A$3:$A$100)/(ISNA(MATCH($I$3:$I$100,O$6:O20,0))),1))),"")
Q7:Q21	Q7	=IFERROR(IF(COUNTIFS($A:$A, $N7,$A:$A,"<>")>=2,SUMIF(A:A,$N7,L:L),(INDEX(L:L,MATCH(N7,A:A,0)))),"")
Q22	Q22	=SUM(IF(ISERROR(Q7:Q21),"",Q7:Q21))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Z51]

In R7 and filled down, this formula will give you the Invoice # of the last non-blank cell in column N. Not sure if that's what you're after, though.

=LOOKUP(2,1/($G$7:G7<>""),$G$7:G7)

By extension, this would give you the address of the cell that contains that Invoice #:

=ADDRESS(MATCH(LOOKUP(2,1/($G$7:G7<>""),$G$7:G7),G:G,0),14)

 

[gtd526]

In R7 and filled down, this formula will give you the Invoice # of the last non-blank cell in column N. Not sure if that's what you're after, though.

=LOOKUP(2,1/($G$7:G7<>""),$G$7:G7)

By extension, this would give you the address of the cell that contains that Invoice #:

=ADDRESS(MATCH(LOOKUP(2,1/($G$7:G7<>""),$G$7:G7),G:G,0),14)

I had to adjust the Column Letter to N:N from G:G.
I incorporated it into my previous formula, and it WORKS!
I can use your first formula or the 2nd one. They both have the same result.
Thank you.

 

[Z51]

Glad it worked. Sorry about the column G mix-up, forgot to adjust that bit.

 

[Peter_SSs]

Wouldn't this very simple operation do the same job?

22 07 30.xlsm
	N	O	P	Q	R
6	Invoice(s)	Summary		Price	Invoice
7	2	Door Hinge		$100.00	2
8	6	Install Lighting		$500.00	6
9		Cabinets			6
10		Mbath			6
11	8	Walls		$280.00	8
12		Floor Rug			8
13	9	Patch Walls		$450.00	9
14		Thin Set Grout			9
15	10	Lights		$300.00	10
16	11	Ceiling		$50.00	11
17		0			11
Inv

Cell Formulas
Range	Formula
R7:R17	R7	=IF(N7="",R6,N7)