Forum Discussion
Formula Function on different sheets
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.
- mathetes
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'm sorry, but I'm not following this at all.I understand the words you're speaking (writing) but can't connect them with what I'm seeing.
For example, in column G, "Units remaining" I see the formula =87-xx where the xx corresponds to the same row in column C. But whence cometh the 87? Why is it the same in Jan3 and Jan10 tabs? Is that an accurate reflection of reality, or just an artifact of a too-simple sample?
What are the colorful numbers in the upper right? They're entered as numbers, but not calculating...what do they represent in connection (or is there one?) with the other numbers.
In your sample--again this may just be a quick sample I realize--the figures under column C are the same in each week. Is that a reflection of reality?
Finally, Where is that new formula needed and why?
Could you walk through, say Bill Nye's two weeks. He starts with XX units, does a,b,c (various activities) (which adds or subtracts? units in column C) and ends the week..... which results in YY Units remaining (??)
And then the next week, Bill starts with YY units (??).
You see what I mean. I don't know where "units" are coming from, where they're going; whether the Bill Nye and Carl Pillow characters (good names, by the way) are adding or consuming units...and what's the "per unit" dollar figure doing in all this, and the referral dates--are they significant?
Who gets billed, by the way? A client? Bill Nye et al?
Thanks for the reply. I'll have to check with my boss if I can upload. If I can, I will tag you. Thanks.
