Formula cell references change when using ctrl + X and ctrl + V or drag and drop.

[stanew]

I am confused why this is even happening. I have a sheet with a list of names that I ref. on other sheets to complete forms. When I drag or use ctrl+X to move a name on sheet1 to a different row the formulas on my forms (another sheet) referencing the cell of the name I moved changes to the cell where the name is dropped or ctrl+V'd. Relative and absolute referencing makes no difference in the formulas. I don't understand why the formula is tied to the data on sheet1 and not to the cell its self. I normally lock in the column when I copy a formula down rows. I waa experimenting with relative and absolute ref's in the formulas below. Sorting the list of names and ctrl+c - ctrl +v has no affect on the formulas on sheet 2. Thanks in advance for your help.

TrainingRosterTemplate.xlsm
	A	B	C	D	E
1		First name	Last Name	Shop	Class Info
2	1	Billy	Baseball	A-shop	ReadySC IO#
3	2	Larry	Lefty	A-shop	ReadySC Class:
4	3				Course Description :
5	4				Training Location:
6	5				Start Date:
7	6				End Date:
8	7				Start time:
9	8				End Time:
10	9				Training hours
11	10				Instructor 1 :
12	11				Instructor 2 :
13	12				Instructor 3 :
14	13				Workshop Code(s)
15	14
16	15
17	16				Training Supplier
18	17				Supplier Contact
Attendees

TrainingRosterTemplate.xlsm
	A	B	C	D	E	F
8		Trainee Name:		Birth Month/Day	Start Date	End Date
9		Last	First
10	1	Baseball	Billy		6/10/2024	6/11/2024
11	2	Lefty	Larry		6/10/2024	6/11/2024
12	3
13	4
14	5
15	6
16	7
17	8
18	9
19	10
20	11
21	12
22	13
23	14
24	15
25	16
26	17
ReadySC Roster-1

Cell Formulas
Range	Formula
B10	B10	=PROPER(Attendees!$C$2)
C10	C10	=IF(Attendees!$B$2<>"", PROPER(Attendees!$B$2), "")
B11,B19:B26,B13:B17	B11	=IF(Attendees!$C3<>"", PROPER(Attendees!$C3), "")
C11:C26	C11	=IF(Attendees!$B3<>"", PROPER(Attendees!$B3), "")
B12	B12	=PROPER(Attendees!$C4)
B18	B18	=IF(Attendees!$C$10<>"", PROPER(Attendees!$B$10), "")
E10:E26	E10	=IF($C10<>"", Attendees!$F$6, "")
F10:F26	F10	=IF($C10<>"", Attendees!$F$7, "")

 

[dreid1011]

This may not be the most technical answer, but I believe that is the intended behavior. Locking cell references is so you can drag a formula and not have your ranges adjust as you drag. When a formula references a cell, and you then move that cell, the formula is going to update to the new location expecting the data is still intended for use in your formula and maybe you just moved it for aesthetic purposes.

You can use INDIRECT() to keep a cell reference tied to the exact cell location if you absolutely need to. However, INDIRECT() is a volatile function and should be used sparingly to avoid unnecessary recalculations, thus slowing down your workbook.

 

[stanew]

This may not be the most technical answer, but I believe that is the intended behavior. Locking cell references is so you can drag a formula and not have your ranges adjust as you drag. When a formula references a cell, and you then move that cell, the formula is going to update to the new location expecting the data is still intended for use in your formula and maybe you just moved it for aesthetic purposes.

You can use INDIRECT() to keep a cell reference tied to the exact cell location if you absolutely need to. However, INDIRECT() is a volatile function and should be used sparingly to avoid unnecessary recalculations, thus slowing down your workbook.

Thanks dreid1011 for your replay. I opted for the indirect function.. I have used it a couple of times, but didn't realty understand it. I understand it better now. I have a max of 40 rows in two columns that now can feed first and last names into 6 different forms in my workbook without getting #Ref errors in the associated formulas whenever the name entries are manually arranged. .Here is the basic formula i am using. PROPER(TRIM(INDIRECT("Attendees!"&ADDRESS(ROW(),2,3)))). With the limited number of rows, I don't get any appreciable delay in the calculations. Thanks again.

 

[dreid1011]

Thanks dreid1011 for your replay. I opted for the indirect function.. I have used it a couple of times, but didn't realty understand it. I understand it better now. I have a max of 40 rows in two columns that now can feed first and last names into 6 different forms in my workbook without getting #Ref errors in the associated formulas whenever the name entries are manually arranged. .Here is the basic formula i am using. PROPER(TRIM(INDIRECT("Attendees!"&ADDRESS(ROW(),2,3)))). With the limited number of rows, I don't get any appreciable delay in the calculations. Thanks again.

You're welcome, and happy to help.