Nov 10 2022 08:48 AM - edited Nov 10 2022 08:49 AM
We have a group of Excel sheets, which reference one another that we track data on per year. When a new year begins, I need to essentially copy and paste these sheets into the new year's folder. Is there a way to do this and have the references update to the new folder location without having to physically update every cell's reference?
Nov 10 2022 10:26 AM
This sounds like a situation where it might help to re-think the whole approach. Are you (or your organization) open to doing that?
It's not at all uncommon to see situations like this, where the process began as essentially a "computerization" of tracking that was done on paper ledger sheets. When working on paper, it made sense to have separate sheets for each year; in Excel, that practice can actually interfere with (or hinder, make more difficult) Excel's abilities to summarize data, highlight multi-year trends, compare data by year or month, etc.
Excel does very well handling a single database that covers many years of tracking this, that, or the other.
So I'm wondering--if you are open to re-considering the whole approach--whether you could either post a sample or two of these worksheet(s) or, if they contain confidential/proprietary info, describe more fully the nature of the various worksheets and why you've found it necessary to use the current process.
Nov 10 2022 11:51 AM
Nov 10 2022 12:11 PM
Nov 10 2022 12:12 PM
Well, Paul, I would strongly encourage you to inquire. It really sounds as if what happened in the past is that somebody took what I'd call a "paper ledger sheet mental framework" and transferred it to Excel, thinking it could make things easier. Which it can do; at least the basic arithmetic operations can be trusted.
But it also sounds as if it wasn't designed to take full advantage of Excel. Anytime you find yourself needing to copy and paste all kinds of data, and then update formulas to refer to that "old" data, that's a clue that the process could be improved, maybe radically. There are people here in this forum--I being one of them--who'd be happy to help you (or others) re-think how the data are collected and stored (the INPUT) and then how it's used (the OUTPUT) in ways that take full advantage of Excel's many abilities.