Forum Discussion
Formula Function on different sheets
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.
Thank you for your input. I had to include less detail for privacy purposes but I appreciate that you thought about this. I think I will suggest what one of the other contributors mentioned in putting that info on one sheet. Thank you!!
I hope the suggestions were helpful rather than overwhelming. Feel free to come back to this thread or to start another one once you've reorganized the data...
To look ahead, one of the more powerful tools Excel offers for summarizing data like yours could be is that of the Pivot Table. As an example, I use it to produce a monthly summary of both income and dollars spent in an array of about 20 different expense categories (for budget tracking purposes). And those transactions are all in a single database, by date, payee, amount, category....Pivot Table very neatly arrays the information.
Your data is different in its substance, but not (I think) in its basic logic. You've got units coming in (added weekly), and going out, through the efforts of various individuals.... and you want to track those in summary form.
That may be a misunderstanding, I realize, but I suggest it as worth looking at once you have all the data on a single sheet.