Forum Discussion
Formula Function on different sheets
There are multiple sheets for one month. We do a copy and paste each week for the data to transfer to the next sheet. However, I need the formula to change each week to reflect the updated numbers but I can't figure out how to change the formula for each new week (each new sheet.) Is there even a way to do this? Or do I just need to copy/paste the new updated numbers then go back and copy/paste the other information? Looking for the easiest way. Thanks!
10 Replies
I strongly recommend arranging all data in one sheet with a dedicated date column.
Excel has plenty of tools for summarizing your data quickly to give you the records you need and there's no need to copy/paste to transfer data. The ideal arrangement of data is in as few sheets as possible with data running vertically with as few columns as possible.
- Patrick2788
Thank you for your input. I think I will suggest arranging the data on one sheet with a date section. Thank you! What Patrick is recommending is exactly what had in mind back at the start, when I said I was almost certain there was a better way to accomplish what you're trying to do. I've made a start at doing that single sheet, but I need your answers to my questions in the last post.
It's hard at times to re-think the way we do things, but that's exactly what's involved here. You ARE already continuing things almost AS IF it were a continuous table. It's just that you're doing significant parts of it manually, with the copy and paste and whatever you do manually to adjust the number of units. Once it's re-designed it will work more smoothly, but we (I or Patrick, or one of the other experts around here) need to know more about what takes place in that week-to-week transition.
Nicole, I'm sure there IS a way to do what you're asking.
I'm also almost certain that there's a better way to do what you're doing. Any time we get to a manual procedure that involves regular copying and pasting, it's pretty much certain that it's a given: we could do this more efficiently, taking advantage of Excel's abilities with Tables, and data in general.
Is it possible for you to upload a sample of the actual spreadsheets (making sure that any confidential or personal info has been disguised)?
Here is a very shortened version of the report. So each sheet is a different week of the month. Units are added to the 'units' column each week. When we do a copy and paste for a new sheet, the 'units remaining' column doesn't change for the new week (since it's a copy/paste.) We need the new week to reflect a different formula since we are adding in new units that will then change the 'units remaining'. I hope this makes some sense. Any and all help is appreciated.
It occurred to me overnight that your weekly spreadsheets themselves look as if they may well be, in effect, summaries of individual transactions that have happened during the week. So then, if that assumption is correct:
- That would suggest that you're keeping track somewhere of those transactions
- And if that's the case, you could just use Excel to keep track on a daily basis (or even more frequent: transaction by transaction), noting date of transaction, name (or, to make it easier, an ID#), #units, $ involved (if any), etc. whatever you track on a transaction-by-transaction basis
- And then the transaction of adding in more units would become just another transaction (though we need to clarify what adding units actually means (i.e., is that a corporate action affecting all equally or do various individuals have units added distinctly to individual accounts? or something else?)
And then Excel's marvelous abilities to summarize transactions to produce that weekly summary, or even an ongoing --all on one page--summary of the last NN weeks/months/quarters. Right now you're doing a lot of work manually that Excel could be doing for you--at least that's what appears to be happening.
Thanks for the reply. I'll have to check with my boss if I can upload. If I can, I will tag you. Thanks.
