Forum Discussion
Group of Excel Sheets Referencing Each Other
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.
I'm fairly new to the company, so I've adopted the process that's been in place for years. It would be above my pay grade to decide to change the process, but I can certainly inquire. There is proprietary info in the sheets, so wouldn't be able to share, but essentially there are several dept's that input their daily data into their respective sheets and then a couple other sheets where the data is consolidated for easy viewing. So far, I've copy and pasted the sheets into a new folder and started updating the references to the new destination name and it just seems like there should be an easier way to do it. At this rate, it will take a full week to manually change each cell.
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.