Sep 21 2021 11:24 AM
Sep 21 2021 11:24 AM
Hello, I need help with a workbook. Each month I want to click a button to start a new month - by starting a new month I want all figures listed in the column named, "current disbursement" to be moved into the "previous fundings" column. I will enter my new current numbers manually.
With the next month, how do I add what was already listed in previous fundings to what needs to be moved over from current?
As soon as I zero out the current column, my previous fundings column takes its cue and zero's itself out as well.
If I manually enter the number into the previous fundings column, excel won't know that next time I want to add it to something else.
Thank you in advance for any suggestions!
Sep 21 2021 12:08 PM
Oh, this raises so many questions in my mind.
I could go on.
I'm a person with a database background. I see a spreadsheet such as you have here as a summary of a bunch (pretty surely a BIG BUNCH) of other transactions. Transactions the details of which you'd also want to be able--I would think--to report on or supply as backup to this kind of monthly/quarterly summary. Obviously (I think) there is a set of categories off to the left that have intentionally been removed from the test sheet you've posted; that's fine.....need to preserve confidentiality, and I respect that...
A database could be used as the background to this as a "dashboard" spreadsheet that retrieves the data on all those transactions and presents a snapshot for any given month "on demand."
Is that in fact what's happening? It doesn't sound like it, given both your questions and your description of adding numbers manually into various rows.
So I'm asking just to probe whether or not there's any interest in a different approach altogether. If not, that's fine.....I have to defer to others anyway when VBA is involved; as noted, my perspective is that of a database person.
Sep 21 2021 12:23 PM
Sep 21 2021 12:47 PM
Well, I'm glad you're open. I'm--long retired--not in the business of consulting on and helping clients create advanced accounting databases and monthly summary reports. I can pretty confidently assure you that you'd be far better served by building a solid database in the background, a database in which you record the individual transactions that constitute the "bunches" of data that lie behind these summary numbers.
As a trivial example of the kind of thing I'm talking about, I have a database that consists of every check written, every income item received, every credit card payment (downloaded from the various banks in every case)....I add budget categories to each line of the database...and then use Excel (Pivot Table, generally) to summarize monthly totals for each budget category. I can (through the magic of the Pivot Table) get instant backup details on any monthly figure in any budget category. How many macros are involved in this workbook? Zero. It's all done with standard, built-in Excel functions. Not to say that macros or VBA routines aren't useful; they are when needed. But they're often not needed, and (potentially) less reliable, if only because less tested, than standard built-in routines.
So I don't know what your budget is to develop this in a different way, and I'd be happy to give a more detailed description of what I'm hinting at, but I think you and your organization would be well served by taking a step back from the monthly summary itself to design a database as the foundation of a more robust monthly reporting system.
Sep 21 2021 01:49 PM
@mathetes We do have a GL system which serves as the larger database for global info - capabilities of sorting on the totals that have been spent on one code or another. This sheet really needs to represent only the current request and the summary totals of previous requests. The secondary piece would purely be there so everyone can see we didn't spend more than the agreed upon amount per line item - or to prove that we do have a few dollars left for that line item. Thank you for your input! I will definitely continue brainstorming on how to make this even better