Please i need help with proper functions

[kvemperor]

I am shop owner and this is a sample so i know from my suppliers what i owe .i am writing the invoice number and the invoice value and how much money i am giving, so i know always the balance(how much i still owe every time).
How can i make the number of invoice to be in the same row with the transaction number, and the number of paydown payment to be also, with its own transaction number,(columns A and C and columns D and E be parallelized)
In cell A10 i have out the form
=IFERROR("Νο. " & SEQUENCE(COUNTA(C10:C1000));" ")
In cell D10 i have put the form
=IFERROR("Νο. " & SEQUENCE(COUNTA(E10:E1000));" ")

Ι found a solution with theses functions
cell A10
=IF(C10="","","Νο."&COUNTA($C$10:C10))
and
cell D10
=IF(E10="","","Νο."&COUNTA($E$10:E10))
but these functions need to be dragged down.i need to be spilled instead.
It would be more handy instead of dragged down be spilled.its more automatic
I am thinking from a combination with the sequence function
Thank you in advance

CLIENT NAME .xlsx
	A	B	C	D	E	F	G	H	I
1	ΝΑΜΕ OF CUSTOMER
2
3
4
5
6
7	TRANSACTION NUMBER	VALUE OF INVOICE	NUMBER OF INVOICE	TRANSACTION NUMBER	VALUE OF PAYDOWN PAYMENT	DATE	NEW BALANCE
8
9							- €
10	Νο. 1	500,00 €	INVOICE No 5	Νο. 1		05/1/2023	500,00 €
11	Νο. 2			Νο. 2	300,00 €	06/1/2023	200,00 €
12	Νο. 3	500,00 €	INVOICE No 6			07/1/2023	700,00 €
13		500,00 €	INVOICE No 7			08/1/2023	1.200,00 €
14					100,00 €	09/1/2023	1.100,00 €
15
16
17
18
ΕΤΑΙΡΙΑ

Cell Formulas
Range	Formula
A10:A12	A10	=IFERROR("Νο. " & SEQUENCE(COUNTA(C10:C1000))," ")
D10:D11	D10	=IFERROR("Νο. " & SEQUENCE(COUNTA(E10:E1000))," ")
G9:G14	G9	=B9+G8-E9
Dynamic array formulas.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

I Need The Proper Functions

i am shop owner and this is a sample so i know from my suppliers what i owe .i am writing the invoice number and the invoice value and how much money i am giving so i know always the balance(how much i still owe every time). What functions should I write in the cells A10 and D10 so I can get...

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[hydraulicwave]

It sounds like you want to use a dynamic array formula that automatically spills down the "Νο." and transaction number when new data is added to columns C and E. You can use the FILTER function in combination with the SEQUENCE function to achieve this.

For column A, you can use the following formula: =IF(C10:C1000="","",FILTER(SEQUENCE(COUNTA(C10:C1000)),C10:C1000<>""))

For column D, you can use the following formula: =IF(E10:E1000="","",FILTER(SEQUENCE(COUNTA(E10:E1000)),E10:E1000<>""))

This will automatically spill down the "Νο." and transaction numbers in columns A and D whenever new data is added to columns C and E.

Please note that this uses dynamic array formula which are only supported in Office 365 and later versions of excel

 

[kvemperor]

It sounds like you want to use a dynamic array formula that automatically spills down the "Νο." and transaction number when new data is added to columns C and E. You can use the FILTER function in combination with the SEQUENCE function to achieve this.

For column A, you can use the following formula: =IF(C10:C1000="","",FILTER(SEQUENCE(COUNTA(C10:C1000)),C10:C1000<>""))

For column D, you can use the following formula: =IF(E10:E1000="","",FILTER(SEQUENCE(COUNTA(E10:E1000)),E10:E1000<>""))

This will automatically spill down the "Νο." and transaction numbers in columns A and D whenever new data is added to columns C and E.

Please note that this uses dynamic array formula which are only supported in Office 365 and later versions of excel

I have version office 2021.it writes to me value error.i tryed to open it also with excel web version and gives me the same error

 

[kvemperor]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

I Need The Proper Functions

i am shop owner and this is a sample so i know from my suppliers what i owe .i am writing the invoice number and the invoice value and how much money i am giving so i know always the balance(how much i still owe every time). What functions should I write in the cells A10 and D10 so I can get...

chandoo.org

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Ok.i am sorry.i didn't knew that.I will know it for future posts
If i put an other thread here and the same thread get posted also in other site or forum ,i will put the link of the other thread here