Increasing Cell References by 1 in when using autofill over multiple rows

[JDDrcar]

I am pulling my hair out on this on, and would love any fresh ideas!

I have a sheet where each row contains customer information, property details, and pricing for their fertilizer programs, among other details for their entire landscape maintenance program.
On a separate worksheet, I am trying to create a printable sheet for our fertilizer techs that contains all of the pertinent information from those rows. This form with all of it's functions is 41 rows in height.

When I select the form, and try to extend it down, it jumps all of the cell refences. Example: The Customer Name goes from Abrams ('JobData'!A3) to Frankel ('JobData'!A44) I need it to go from Abrams ('JobData'!A3) to Audino('JobData'!A4)

I am hoping to be able to extend the form down, to create 200 auto-completed/ready to print forms for all of our customers.


I really hope I was able to verbalize what we're trying to do here, and if I need to share more detail, please let me know!
Thanks for any help!
JD

 

[zero269]

I really hope I was able to verbalize what we're trying to do here, and if I need to share more detail, please let me know!
Thanks for any help!

Hi JD,

I would start with using this Excel Add-in provided by this forum to make it easier to copy/paste what you want to share from your workbook.
This way they can look at the existing formulas to better analyze it.
It's located here: XL2BB - Excel Range to BBCode

 

[JDDrcar]

Thanks, way more useful than screenshots. This is the 'printable form' that I am trying to create. Ideally, I want to be able to select A1 through G41, grab the fill handle, and drab down, creating sheets that pull each customer's info (located in the rows of another sheet).

2023 Maintenance Compendium.xlsx
	A	B	C	D	E	F	G
1
2		Basic Residential				Abrams
3		2,300 Sq. Ft.				1234 Montgomery Rd.
4						Shaker Heights
5
6		Round 1	Date
7		$ 60	Wind
8			Temp	°
9
10
11		Round 2	Date
12		$ 60	Wind
13			Temp	°
14
15
16		Round 3	Date
17		$ 60	Wind
18			Temp	°
19
20
21		Round 4	Date
22		$ 60	Wind
23			Temp	°
24
25
26		Round 5	Date
27		$ 60	Wind
28			Temp	°
29
30
31
32
33
34
35
36
37
38
39
40
41
Fert Logs

Cell Formulas
Range	Formula
B2	B2	=IF('Job Data'!BG3="Basic Com.", "Basic Commercial", IF('Job Data'!BG3="Basic Res.", "Basic Residential", IF('Job Data'!BG3="Prem. Res.", "Premium Residential", "")))
B3	B3	=('Job Data'!BJ3)
F2	F2	=('Job Data'!A3)
F3	F3	=('Job Data'!B3)
F4	F4	=('Job Data'!C3)
B6	B6	=IF('Job Data'!BG3="Basic Com.", "Round 1", IF('Job Data'!BG3="Basic Res.", "Round 1", IF('Job Data'!BG3="Prem. Res.", "Round 1", "")))
C6	C6	=IF(B6="Round 1","Date","")
B7	B7	=IF('Job Data'!BG3="Basic Com.", 'Job Data'!BL3, IF('Job Data'!BG3="Basic Res.", 'Job Data'!BP3, IF('Job Data'!BG3="Prem. Res.", 'Job Data'!BU3, "")))
C7	C7	=IF(B6="Round 1","Wind","")
C8	C8	=IF(B6="Round 1","Temp","")
D8	D8	=IF(B6="Round 1","°","")
B11	B11	=IF('Job Data'!BG3="Basic Com.", "Round 2", IF('Job Data'!BG3="Basic Res.", "Round 2", IF('Job Data'!BG3="Prem. Res.", "Round 2", "")))
C11	C11	=IF(B11="Round 2","Date","")
B12	B12	=IF('Job Data'!BG3="Basic Com.", 'Job Data'!BM3, IF('Job Data'!BG3="Basic Res.", 'Job Data'!BQ3, IF('Job Data'!BG3="Prem. Res.", 'Job Data'!BV3, "")))
C12	C12	=IF(B11="Round 2","Wind","")
C13	C13	=IF(B11="Round 2","Temp","")
D13	D13	=IF(B11="Round 2","°","")
B16	B16	=IF('Job Data'!BG3="Basic Com.", "Round 3", IF('Job Data'!BG3="Basic Res.", "Round 3", IF('Job Data'!BG3="Prem. Res.", "Round 3", "")))
C16	C16	=IF(B16="Round 3","Date","")
B17	B17	=IF('Job Data'!BG3="Basic Com.", 'Job Data'!BN3, IF('Job Data'!BG3="Basic Res.", 'Job Data'!BR3, IF('Job Data'!BG3="Prem. Res.", 'Job Data'!BW3, "")))
C17	C17	=IF(B16="Round 3","Wind","")
C18	C18	=IF(B16="Round 3","Temp","")
D18	D18	=IF(B16="Round 3","°","")
B21	B21	=IF('Job Data'!BG3="Basic Com.", "Round 4", IF('Job Data'!BG3="Basic Res.", "Round 4", IF('Job Data'!BG3="Prem. Res.", "Round 4", "")))
C21	C21	=IF(B21="Round 4","Date","")
B22	B22	=IF('Job Data'!BG3="Basic Com.", 'Job Data'!BO3, IF('Job Data'!BG3="Basic Res.", 'Job Data'!BS3, IF('Job Data'!BG3="Prem. Res.", 'Job Data'!BX3, "")))
C22	C22	=IF(B21="Round 4","Wind","")
C23	C23	=IF(B21="Round 4","Temp","")
D23	D23	=IF(B21="Round 4","°","")
B26	B26	=IF('Job Data'!BG3="Basic Com.", "", IF('Job Data'!BG3="Basic Res.", "Round 5", IF('Job Data'!BG3="Prem. Res.", "Round 5", "")))
C26	C26	=IF(B26="Round 5","Date","")
B27	B27	=IF('Job Data'!BG3="Basic Com.", "", IF('Job Data'!BG3="Basic Res.", 'Job Data'!BT3, IF('Job Data'!BG3="Prem. Res.", 'Job Data'!BY3, "")))
C27	C27	=IF(B26="Round 5","Wind","")
C28	C28	=IF(B26="Round 5","Temp","")
D28	D28	=IF(B26="Round 5","°","")
B31	B31	=IF('Job Data'!BG3="Basic Com.", "", IF('Job Data'!BG3="Basic Res.", "", IF('Job Data'!BG3="Prem. Res.", "Round 6", "")))
C31	C31	=IF(B31="Round 6","Date","")
B32	B32	=IF('Job Data'!BG3="Basic Com.", "", IF('Job Data'!BG3="Basic Res.", "", IF('Job Data'!BG3="Prem. Res.", 'Job Data'!BZ3, "")))
C32	C32	=IF(B31="Round 6","Wind","")
C33	C33	=IF(B31="Round 6","Temp","")
D33	D33	=IF(B31="Round 6","°","")
B36	B36	=IF('Job Data'!BG3="Basic Com.", "", IF('Job Data'!BG3="Basic Res.", "", IF('Job Data'!BG3="Prem. Res.", "Round 7", "")))
B37	B37	=IF('Job Data'!BG3="Basic Com.", "", IF('Job Data'!BG3="Basic Res.", "", IF('Job Data'!BG3="Prem. Res.", 'Job Data'!CA3, "")))
D38	D38	=IF(B36="Round 7","°","")
C36	C36	=IF(B36="Round 7","Date","")
C37	C37	=IF(B36="Round 7","Wind","")
C38:C39	C38	=IF(B36="Round 7","Temp","")

 

[JDDrcar]

I have been trying out different ideas using the Offset function, and feel like that is getting me closer, but still cant get the info to fill in the way I want it to using the fill handle. Anyone have any other thoughts that I can throw at this?