Nov 06 2021 11:02 PM
Hey I have a project I'm doing which involves Auto filling data from a different workbook and I feel like there's a shortcut to what I'm doing.
=Sheet16!D2 and then I skip a couple rows to find the value from =Sheet16!H2 and then =Sheet16!L2 etc. etc.
I feel like there must be something in fill so I don't need to complete this respective process but I'm not sure and would love any feedback.
It's located on the MonthyAccounts sheet
Nov 06 2021 11:52 PM
@JackReed You are making it so difficult for yourself. And you already experienced what it leads to. You are gathering transactional data in way how you would do in a paper notebook. On page for every month and then add up every page and transfer totals to another page. It goes to far to redesign your whole schedule, but I've added a sheet with a structured table that collects all expenses, but date. Just enter them as they incur. Excel can do the grouping by month for based on the date entered. Then create a pivot table that summarises the expenses in the blink of an eye. No formulae needed. Add expenses as they incur, refresh the pivot table, done.
Nov 07 2021 12:07 AM
Nov 07 2021 02:27 AM
@JackReed I understood your intentions, but as an accountant I'm horrified by the schedule and I merely tried to convince you to change your approach. The way you have set-up the report is prone to error and difficult to maintain (as you already experience yourself).
Attached, you'll find an updated file showing what a few structured tables can do with the help of Power Pivot (i.e. the Data Model). It's a very quick-and-dirty example, but I would prefer to work with these. But it's of course up to you to decide how you want to control the budget for your sports club.
Nov 07 2021 10:09 AM
@Riny_van_Eekelen Don't worry as this project is only for practice give by a professor of mine. I like the pivot table that you made but how am I able to add data from future months as the pivot table is very reluctant to new data
Nov 07 2021 10:21 AM
@JackReed Just type new expense transactions to the first table. It will expand automatically. Then, press Refresh All on the Data ribbon.