SOLVED

How to avoid linking back to original sheet when cutting and pasting rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2232007%22%20slang%3D%22en-US%22%3EHow%20to%20avoid%20linking%20back%20to%20original%20sheet%20when%20cutting%20and%20pasting%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2232007%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20cutting%20rows%20representing%20days%20in%20single%20years%20from%20a%20master%20sheet%20of%2030%20years%20and%20pasting%20them%20into%20their%20own%20sheet%20for%20their%20year%2C%20all%20in%201%20workbook%2C%20so%20I%20will%20end%20up%20with%2030%20sheets.%20But%20when%20I%20do%20that%20the%20formulas%20in%20those%20rows%20now%20refer%20back%20to%20master%20sheet%20that%20they%20were%20cut%20from%20...for%20instance%20instead%20of%20%3DN8%2BG%242%20which%20is%20what%20is%20in%20original%20%22master%22%20sheet%2C%20it%20pastes%20it%20into%20new%20sheet%20as%20%3DN8%2Bmaster!G%242%20But%20I%20just%20want%20it%20to%20paste%20as%20the%20original%20%3DN8%2BG%242%3C%2FP%3E%3CP%3EI%20went%20into%20Data%20but%20%22Edit%20Links%22%20is%20not%20lit%20up%2Favailable.%3C%2FP%3E%3CP%3EHow%20can%20I%20prevent%20that%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2232007%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
Occasional Contributor

I am cutting rows representing days in single years from a master sheet of 30 years and pasting them into their own sheet for their year, all in 1 workbook, so I will end up with 30 sheets. But when I do that the formulas in those rows now refer back to master sheet that they were cut from ...for instance instead of =N8+G$2 which is what is in original "master" sheet, it pastes it into new sheet as =N8+master!G$2 But I just want it to paste as the original =N8+G$2

I went into Data but "Edit Links" is not lit up/available.

How can I prevent that? Thanks.

4 Replies
With your permission, if I can recommend you, add a file (without sensitive data) to your project.
Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.
At the same time, it is much easier for someone who wants to help to understand the subject.
A win-win situation for everyone.
Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

* Knowing the Excel version and operating system would also be an advantage.

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)
best response confirmed by Philster (Occasional Contributor)
Solution

@Philster Try copy and paste, thus NOT cut and paste. Make sure that G2 in your destination sheets contains the same value as on your Master sheet.

 

Otherwise copy and paste values if the historic values are supposed to be fixed. You'll loose the formulae and keep the values.

 

Having said all that, I wonder why you are even splitting data into 30 sheets. Excel is very well equipped to summarise large data sets covering many years. That will be more difficult if you have it in 30 different sheets. 

 

 

Yes, thanks, copy and paste worked. I was working off a "sacrificial" sheet which was copy of master in order to not affect master. I was cutting and pasting year by year when problem showed. I did what you said, copying and pasting year by year, and formulas now don't refer back to original sheet. I never would have thought cut vs copy would matter in that sense.

As for why I am doing 30 separate years, each year has a dozen different such "G" numbers in g1 g2 g3 etc, so if I did it in one large sheet I would have to find the correct spot in 30 year list to add that years list of "g" numbers, plus then go to corresponding cell in a dozen columns and put in correct formula that refers to that g number. By splitting into 30 I can quickly just paste the list into same spot on each years sheet (after the split) and not have to change those cells formula in each year.