cutting and pasting informatio in cells without affecting the formulas that refer to those cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2364223%22%20slang%3D%22en-US%22%3Ecutting%20and%20pasting%20informatio%20in%20cells%20without%20affecting%20the%20formulas%20that%20refer%20to%20those%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2364223%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20list%20of%20names%20and%20each%20name%20has%20a%20number%20associated%20with%20it.%26nbsp%3B%20This%20is%20two%20columns%3A%3C%2FP%3E%3CP%3EA%3A%20Name%20(text)%3C%2FP%3E%3CP%3EB%3A%20Hours%20(number)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20have%20formulas%20in%20columns%20C%20and%20D%20that%20refer%20to%20A%20and%20B%20and%20also%20refer%20to%20one%20cell%20%22G2%22where%20an%20absolute%20amount%20has%20been%20entered%3A%20%22total%20tips%22%3A%26nbsp%3B%3C%2FP%3E%3CP%3EC%3A%20is%20the%20percent%20of%20the%20Total%20hours%20(B2%2Fsum(B2%3AB1000)%20each%20person%20worked.%3C%2FP%3E%3CP%3ED%20is%20the%20percent%20of%20the%20Total%20Tips%3A%20B2*%24E%241%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20attached%20sheet.%26nbsp%3B%20The%20problem%20is%20that%20every%20week%2C%20the%20names%20change%20(people%20quit%20and%20are%20hired)%20but%20the%20list%20has%20to%20be%20in%20alphabetical%20order.%26nbsp%3B%20If%20I%20cut%20and%20paste%20the%20list%20as%20I%20go%20down%20it%2C%20the%20formulas%20in%20columns%20C%20and%20D%20get%20messed%20up%20because%20they%20continue%20to%20refer%20to%20the%20cell%20that%20used%20to%20be%20next%20to%20them.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%20wanted%20to%20add%20%22Love%2C%20Caleb%22%20to%20the%20list%20on%20row%206%2C%20I%20would%20want%20to%20cut%20A6%3AB7%20and%20move%20it%20down%20one.%26nbsp%3B%20%26nbsp%3BProblem%20is%20that%20when%20I%20do%20that%2C%20the%20formulas%20in%20C6%3A9%20and%20D6%3A9%20no%20longer%20refer%20to%20the%20cells%20on%20their%20row.%26nbsp%3B%20And%20if%20I%20protect%20the%20formulas%20in%20the%20C%20and%20D%26nbsp%3B%20using%20%22locked%22%20cell%20and%20Protect%20sheet%2C%20it%20still%20happens.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20no%20way%20to%20set%20the%20formulas%20in%20columns%20C%20and%20D%20so%20that%20they%20ALWAYS%20refer%20to%20the%20cells%20on%20their%20same%20row%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20you%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2364223%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2364344%22%20slang%3D%22en-US%22%3ERe%3A%20cutting%20and%20pasting%20informatio%20in%20cells%20without%20affecting%20the%20formulas%20that%20refer%20to%20those%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2364344%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1052471%22%20target%3D%22_blank%22%3E%40jsifly%3C%2FA%3E%26nbsp%3BTransforming%20the%20table%20to%20a%20structured%20Excel%20table%20(Ctrl-T)%20and%20using%20structured%20references%20could%20help.%20I%20note%20you%20had%20the%20sheet%20protected.%20If%20you%20want%20to%20keep%20that%20you%20can%20drag%20A6%3AB7%20to%20down%20one%20row.%20But%20you%20can't%20insert%20a%20row.%20You'll%20see%20that%20the%20formula%20on%20row%206%20will%20return%20zero%20until%20you%20put%20something%20in%20B6%20and%20the%20formulae%20on%20rows%207%20and%208%20have%20followed%20one%20row%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have list of names and each name has a number associated with it.  This is two columns:

A: Name (text)

B: Hours (number)

 

Then I have formulas in columns C and D that refer to A and B and also refer to one cell "G2"where an absolute amount has been entered: "total tips": 

C: is the percent of the Total hours (B2/sum(B2:B1000) each person worked.

D is the percent of the Total Tips: B2*$E$1

 

See the attached sheet.  The problem is that every week, the names change (people quit and are hired) but the list has to be in alphabetical order.  If I cut and paste the list as I go down it, the formulas in columns C and D get messed up because they continue to refer to the cell that used to be next to them. 

 

For example if wanted to add "Love, Caleb" to the list on row 6, I would want to cut A6:B7 and move it down one.   Problem is that when I do that, the formulas in C6:9 and D6:9 no longer refer to the cells on their row.  And if I protect the formulas in the C and D  using "locked" cell and Protect sheet, it still happens. 

 

Is there no way to set the formulas in columns C and D so that they ALWAYS refer to the cells on their same row?

 

Thanks in advance for you help

 

1 Reply

@jsifly Transforming the table to a structured Excel table (Ctrl-T) and using structured references could help. I note you had the sheet protected. If you want to keep that you can drag A6:B7 to down one row. But you can't insert a row. You'll see that the formula on row 6 will return zero until you put something in B6 and the formulae on rows 7 and 8 have followed one row down.