Remove absolute locking from a Range of Cells

Julia Bennfors

I have a file, where I made the mistake of "Absolute locking" all of the formulas, before realizing that I am going to have to duplicate this chart other regions. Below is of "NED." I need to copy 2 more of these tables (for "central" & "west", ideally in the same sheet, so its easier to maintain down the road. As well as create a roll up summary of all 3 at the top

Is there anyway to select a range of cells, and remove the "absolute" / "$" in each formula? or "find/replace" for formulas, like excel has?

My back up plan (thinking out loud) is to just just do a "save as" and create 2 new sheets for the other regions (replace the word "NED" in both), and than another sheet for a roll up of all 3 sheets. I know I cannot create reports to do mass edits, because in all of the light grey cells... there is a Index/Match formula. I am open to any other solution - that works long term & is easy to maintain. Thanks! - Julia

Paul Newcome

Unfortunately you cannot "mass edit" formulas even with a find/replace. You are either going to have to remove the absolutes one by one or you will need to go with your second option of "save as new". I personally try to use the "save as new" for things like this for my own personal organization when linking to summaries and rollups, and referencing for graphs in dashboards and whatnot.