Copy Paste Formula adding extra rows

[ultramel]

Hi there
I have a sheet "invoice template" which contains 6 rows per 'invoice'. Each invoice pulls the reference and the container number from the second sheet ("jobs")
This works in the first 6 rows reference is =jobs!A2,and container is =jobs!B2

However when I copy those 6 rows in 'invoicetemplate" to create the next invoice underneath it (from row7) the formula pastes the reference number as =jobs!A8 instead of =jobs!A3, and container number pastes as =jobs!B8 instead of =jobs!B3

basically the formula is adding the 6 rows each time it is pasted - which it should not do as the items in the job sheet are underneath each other with no open rows.

invoice.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Header			Y	ABC01	1	13/09/2022	JSI22070630	N	0
2	Detail	0	1	526	0	1	15	3	0	ADD	Additional Fees	4
3	Detail	0	1	0	0		0	3	0	'	NYKU 1234567	7
4	Detail	0	1	0	0		0	3	0	'	JSI22070630	7
5	Detail	0	1	0	0		0	3	0	'	Client Name	7
6	Header			Y	ABC01	1	13/09/2022	JSI22090172-01	N	0
7	Detail	0	1	526	0	1	15	3	0	SURD	Surveillance DBN to JHB	4
8	Detail	0	1	0	0		0	3	0	'	KKBU 8029884	7
9	Detail	0	1	0	0		0	3	0	'	JSI22070630	7
10	Detail	0	1	0	0		0	3	0	'	Client Name	7
InvoiceTemplate

Cell Formulas
Range	Formula
H1	H1	=jobs!A2
K3	K3	=jobs!B2
H6	H6	=jobs!A8
K8	K8	=jobs!B8

---

invoice.xlsx
	A	B
1	Reference	Container
2	JSI22070630	NYKU 1234567
3	JSI22080600-01	TCKU 8541112
4	JSI22080604-01	MKSP 1503300
5	JSI22080602-01	FSCU 8459992
6	JSI22090250-01	TLLU 4857838
7	JSI22090138-01	NYMU 9738838
8	JSI22090172-01	KKBU 8029884
jobs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A8	Cell Value	duplicates	text	NO

 

[Joe4]

Welcome to the Board!

Try putting this formula in cell H1 and then copying to H6, H11, etc.

=OFFSET(Jobs!$A$2,INT(ROW()/5),0)

And put this formula in cell K3 and copy to K8, K13, etc.

=OFFSET(Jobs!$B$2,INT(ROW()/5),0)

 

[Fluff]

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: Copy Paste Formula adding extra rows
If you have posted the question at more 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.