Home

copy and paste a range of cells and keep formula eferences

%3CLINGO-SUB%20id%3D%22lingo-sub-478853%22%20slang%3D%22en-US%22%3Ecopy%20and%20paste%20a%20range%20of%20cells%20and%20keep%20formula%20eferences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478853%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20goal%20is%20to%20copy%20a%20range%20of%20cells%20and%20have%20all%20the%20formulas%20in%20the%20copied%20range%20of%20cells%20pasted%20into%20a%20new%20location%20keeping%20the%20original%20formula%20references.%20I.e%2C%20I%20will%20have%20two%20identical%20data%20in%20two%20sets%20of%20cells%20on%20a%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE.g.%20one%20of%20the%20cells%20in%20the%20range%20to%20be%20copied%20might%20have%20a%20formula%20similar%20to%2C%20%22%3D'sheet%202'!K75%22.%20I%20wish%20for%20that%20formula%20to%20be%20pasted%20into%20the%20destination%20cells%20and%20be%26nbsp%3B%22%3D'sheet%202'!K75%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-478853%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-479398%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20a%20range%20of%20cells%20and%20keep%20formula%20eferences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-479398%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299641%22%20target%3D%22_blank%22%3E%40Tim_Parisi%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20use%20absolute%20references%20for%20that%3C%2FP%3E%0A%3CPRE%3E%3D'sheet%202'!%24K%2475%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-479549%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20a%20range%20of%20cells%20and%20keep%20formula%20eferences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-479549%22%20slang%3D%22en-US%22%3EIf%20you%20dislike%20modifying%20your%20references%20from%20relative%20to%20absolute%2C%20select%20the%20original%20cell%2C%20select%20the%20entire%20formula%20therein%20on%20the%20formula%20bar%2C%20press%20Ctrl%2BC%2C%20press%20Esc%2C%20select%20the%20destination%20cell%2C%20press%20Ctrl%2BV%2C%20and%20press%20Esc.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-479572%22%20slang%3D%22en-US%22%3ERe%3A%20copy%20and%20paste%20a%20range%20of%20cells%20and%20keep%20formula%20eferences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-479572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299641%22%20target%3D%22_blank%22%3E%40Tim_Parisi%3C%2FA%3E%26nbsp%3B%2C%20both%20assume%20changes%20cell%20by%20cell.%20To%20copy%20entire%20range%20the%20workaround%20could%20be%3C%2FP%3E%0A%3CP%3E-%20select%20entire%20range%3C%2FP%3E%0A%3CP%3E-%20Ctrl%2BH%20and%20replace%20%3D%20on%20%23%3C%2FP%3E%0A%3CP%3E-%20copy%2Fpaste%20the%20range%20on%20new%20location%3C%2FP%3E%0A%3CP%3E-%20select%20range%2C%20Ctrl%2BH%20and%20replace%20%23%20on%20%3D%20(for%20both%20ranges)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Tim_Parisi
Occasional Contributor

My goal is to copy a range of cells and have all the formulas in the copied range of cells pasted into a new location keeping the original formula references. I.e, I will have two identical data in two sets of cells on a sheet.

 

E.g. one of the cells in the range to be copied might have a formula similar to, "='sheet 2'!K75". I wish for that formula to be pasted into the destination cells and be "='sheet 2'!K75".

3 Replies

@Tim_Parisi , you may use absolute references for that

='sheet 2'!$K$75
If you dislike modifying your references from relative to absolute, select the original cell, select the entire formula therein on the formula bar, press Ctrl+C, press Esc, select the destination cell, press Ctrl+V, and press Esc.

@Tim_Parisi , both assume changes cell by cell. To copy entire range the workaround could be

- select entire range

- Ctrl+H and replace = on #

- copy/paste the range on new location

- select range, Ctrl+H and replace # on = (for both ranges)