Mar 24 2021 02:45 AM
Mar 24 2021 02:45 AM
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.
Mar 24 2021 03:40 AM
Mar 24 2021 03:57 AMSolution
@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.
Mar 24 2021 04:49 AM - edited Mar 24 2021 04:50 AM
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.