0
I have two excel workbooks that are formatted similarly, but have different data in them (one with 2016 data and the other with 2017 data). This means that both workbooks have the same named ranges but they reference different cells (i.e. the named ranges in the 2016 workbook reference the cells in the 2016 workbook, and same for the 2017 workbook).
When I created the 2017 workbook, I copied and pasted in some formulas from the 2016 workbook. This unknowingly kept the named ranges that referenced the 2016 workbook instead of just using the named range with the identical name in the 2017 workbook.
Now, when I go to name manager in the 2017 workbook, I have duplicates of all of my named ranges with one referencing cells in the 2017 workbook and the other referencing cells in the 2016 workbook.
I want to replace all of the references to the named ranges that reference the 2016 workbook with the named ranges of the same name that reference the 2017 workbook. How would I go about doing this?
user7758051
Posted 2018-03-29T18:59:59.840
Reputation: 55
When copying cells that are looking for ranges, I believe the formula will automatically refer to the original worksheet (ie, the problem you're having). One workaround I've used is to copy the formulas out of my original sheet, paste them in notepad, then copy out of notepad and paste into the new workbook. This will bring over just the formula as it's written without Excel trying to keep the original sheet linked. I'm sure there's way to change all the references in the new workbook, but this method worked pretty well for me. – jrichall – 2018-03-30T16:59:47.453
Old question, but for reference: The named ranges referring to 2017 would be just the
Namein the 2017 workbook, but the ones referring to 2016 would look likeWorkbook2016!Name. So can't you just do a standard Search and Replace of Workbook2016! with nothing? It worked in my testsheet. – Joost – 2019-01-24T15:47:52.347