nervousenergy
New Member
- Joined
- Dec 17, 2013
- Messages
- 4
Hey.</SPAN>
I'm using Excel 2007.</SPAN>
I've cross-posted to Reddit, here. </SPAN>
Attached is the spreadsheet I've created, with some fake data and any identifiable info removed. https://www.dropbox.com/l/yy2nXCSVcV2B4BbtWc6sE8
I have been tasked with creating a spreadsheet for my work, which would allow us to input in invoice details for various Sports Clubs.</SPAN>
Introduction</SPAN>
There are several sports clubs we spend money on. We can spend money in various categories (Promotion, Equipment, etc). We have several set budgets that the money can be drawn from. </SPAN>
I've tried to create a spreadsheet which allows us to do this and tottle up all the figures to display (See Totals worksheet). The main aim of the spreadsheet was to be able to nicely see how much we spent, where, what on, and for what sport. </SPAN>
I should preface by saying I've *very* little knowledge of Excel, and what you can see in the spreadsheet I exampled was what I could cobble together after days of tinkering</SPAN>
What I've got so far is.. okay. But I'm really not happy with it. </SPAN>
Problems</SPAN>
Scroll to the sides of each "Sport" sheet, and you can see lots of columns that I use to work out the calculations. What the columns do is check each "live" column in turn (Columns B to G) for a "Budget Name" (which is in the dropdown box), and if theres a figure above it. If there is, then it copies the figure and totles it up.</SPAN>
That is a needlessly complicated way of doing it, but I can't figure out a better way. What I wanted to achieve was for the figure to be linked to the "Budget Name" so that the sheet knows what a figure is linked to a certain budget. </SPAN>
Another problem is: check out the formula on the pink Total row in any of the "Sports" sheets. It's a horrible MODROW formula that totles up every seventh row (every "Amount" in the columns). Is there an easier way of doing this?</SPAN>
Another problem: The budget names derive from a dropdown list, which derives from the "Budgets" list on the "Totals" page. This works, except if you change one of the Budget names on this list (i.e. change Budget A to Sport Budget), it doesn't carry across to the other sheets which use that name. Is there a way to automatically update all the names of the Budgets so that they are consistent?</SPAN>
And finally.. is there just a better way of showing and inputting and calculating the data as a whole? I thought a great way would be to use a Form i.e. Microsoft Access, where you put in invoice details individually and then that carries over and automatically updates a Totals view. Seems that Excel doesn't support this though. Would VBA help? (I've zero experience of the VBA editor).</SPAN>
Can anyone help design me a better spreadsheet? I hope I'm not asking too much, but I've been stressing over this for a few weeks now, and it's getting to the point where we NEED to start putting actual data into it. </SPAN>
A bit of a flaw in the whole sheet is that the allocated Budgets need to be flexible. They're also talking about wanting to allocate bits of the set budgets to various individual sports, and to be able to calculate a "projected" spend and an "actual" spend. I've no idea how I could get that to work.</SPAN>
This is all horrendously complicated, isn't it? At least, it is to my little mind. Have a look over the spreadsheet I exampled, and ask me to expand on anything I've said. I'd really appreciate some help. </SPAN>
Thanks!</SPAN>
I'm using Excel 2007.</SPAN>
I've cross-posted to Reddit, here. </SPAN>
Attached is the spreadsheet I've created, with some fake data and any identifiable info removed. https://www.dropbox.com/l/yy2nXCSVcV2B4BbtWc6sE8
I have been tasked with creating a spreadsheet for my work, which would allow us to input in invoice details for various Sports Clubs.</SPAN>
Introduction</SPAN>
There are several sports clubs we spend money on. We can spend money in various categories (Promotion, Equipment, etc). We have several set budgets that the money can be drawn from. </SPAN>
I've tried to create a spreadsheet which allows us to do this and tottle up all the figures to display (See Totals worksheet). The main aim of the spreadsheet was to be able to nicely see how much we spent, where, what on, and for what sport. </SPAN>
I should preface by saying I've *very* little knowledge of Excel, and what you can see in the spreadsheet I exampled was what I could cobble together after days of tinkering</SPAN>
What I've got so far is.. okay. But I'm really not happy with it. </SPAN>
Problems</SPAN>
Scroll to the sides of each "Sport" sheet, and you can see lots of columns that I use to work out the calculations. What the columns do is check each "live" column in turn (Columns B to G) for a "Budget Name" (which is in the dropdown box), and if theres a figure above it. If there is, then it copies the figure and totles it up.</SPAN>
That is a needlessly complicated way of doing it, but I can't figure out a better way. What I wanted to achieve was for the figure to be linked to the "Budget Name" so that the sheet knows what a figure is linked to a certain budget. </SPAN>
Another problem is: check out the formula on the pink Total row in any of the "Sports" sheets. It's a horrible MODROW formula that totles up every seventh row (every "Amount" in the columns). Is there an easier way of doing this?</SPAN>
Another problem: The budget names derive from a dropdown list, which derives from the "Budgets" list on the "Totals" page. This works, except if you change one of the Budget names on this list (i.e. change Budget A to Sport Budget), it doesn't carry across to the other sheets which use that name. Is there a way to automatically update all the names of the Budgets so that they are consistent?</SPAN>
And finally.. is there just a better way of showing and inputting and calculating the data as a whole? I thought a great way would be to use a Form i.e. Microsoft Access, where you put in invoice details individually and then that carries over and automatically updates a Totals view. Seems that Excel doesn't support this though. Would VBA help? (I've zero experience of the VBA editor).</SPAN>
Can anyone help design me a better spreadsheet? I hope I'm not asking too much, but I've been stressing over this for a few weeks now, and it's getting to the point where we NEED to start putting actual data into it. </SPAN>
A bit of a flaw in the whole sheet is that the allocated Budgets need to be flexible. They're also talking about wanting to allocate bits of the set budgets to various individual sports, and to be able to calculate a "projected" spend and an "actual" spend. I've no idea how I could get that to work.</SPAN>
This is all horrendously complicated, isn't it? At least, it is to my little mind. Have a look over the spreadsheet I exampled, and ask me to expand on anything I've said. I'd really appreciate some help. </SPAN>
Thanks!</SPAN>
