I've created a cashbook excel worksheet for clients. Rows 3 down to row 30 has formulas in them. I've created a button and want to assign a macro that will insert say 10 cells at a time below row 30, that automatically include the formulas in the previous rows.
My worksheet has 30 rows and foot totals at row 31, but the idea of the button/macro is so that the client can press it and he will have 10 additional rows, with formulas, so he can enter more transactions.
Google is proving to be NOT my friend on this one.
Can anyone point me toward a code that will achieve this ?
Many many thanks
Replies (10)
Please login or register to join the discussion.
Have you tried
recording a macro to achieve what you want using Relative rather than Absolute references and then editing the result?
Perhaps consider putting the totals at the top rather than the bottom, so running something like:
Row 1 - Totals as sum of the data belowRow 2 - Headings with the view frozen to always show rows 1 & 2Row 3 et seq - Input area with formulae.
That would save you having to move the foot totals each time you insert rows.
You can make the top row SUM() expand to cover new rows added with a formula as below, which assumes that Column A will always contain a narrative entry
Sum for Column B:
=SUM(OFFSET(B2,0,0,COUNTA($A:$A),0))
If you are using Excel 2007 onwards you might consider formatting as a table. Then you can set the bottom row as a TOTALS row that expands as you add rows above, AND the cells that contain formulae should extend the formulae as you add rows.
Yes
It even saves you having to freeze rows as the table header rows become the entries in the Row names (A:B:C) when you get down the page and they would normally disappear.
Give the table a recognisable name, if for example you are going to have more than one table in the workbook, then you can refer to it in formulae with the table name and referencing the header names for the table without having to create named ranges
So you might call your table tblCashBk and have the headers Date : Narrative : Amount
If elsewhere, outside the table, you wanted a cell to sum the amounts between 1 Jan 2013 & 10 Jan 2013 then you could use the formula
=SUMIFS(tblCashBk[Amount],tblCashBk[Date],">=01/01/2013",tblCashBk[Date],"<=10/01/2013")
Which makes reading your formula easier.
Your problem in sending the workbook to clients would be if they had a much older version of Excel that did not support Tables
Tables is a good
option but be aware you cannot protect them easily and add rows*. You will probably need a macro that temporarily unprotects the sheet when a new row is added or else leave the sheet unprotected which, given that you have formuale, may be undesirable.
*Does anybody know whether this has changed in Excel 2013?
Office 2013
I've not got that far having only downloaded 2013 last week and the dummy sheet I was tinkering with last night was not a protected sheet. Dragging down on the botton cormer of the table in an unprotected sheet adds rows easily. I shall have to have a further play over the w/eoption but be aware you cannot protect them easily and add rows*. You will probably need a macro that temporarily unprotects the sheet when a new row is added or else leave the sheet unprotected which, given that you have formuale, may be undesirable.
*Does anybody know whether this has changed in Excel 2013?
The 2013 suite does seem a little flaky at times working with my Avast AV software. I have had to turn off Avast in Outlook as it just crashed every time I clicked send on an email :(
Is this cost/time effective ....
Sorry to put a damper on this thread, but with all the 'free' software about (desktop & Cloud), why would you you spend chargeable time 'playing' at programming. Other 'professsional' developers have probably done it better and have a proven product
'playing' is used advisedly because we have all come across those whose primary discipline is not programming (accounting) - Furthermore, one only has to see the 'firestorm' on this site if an unqualified person makes a perfectly reasonable request for advice about starting up on their own as an accountant / bookkeeper etc. (there was a thread recently)
The fact that today’s software tools have removed a lot of the complexities of programming, doesn't mean that training can be ignored - and no a 'binary chop' is not something you have for dinner
Have you had training in aspect of programming or simply jumped straight in?What is your testing/release procedure?Have you written a proper user manual?What about update procedures over potentially many clients using your spread sheet - how do you roll out an update, update manuals, run a help desk etc..?How do you propose to audit/validate your work is correct and get a 3rd party to check it. Excel is notorious for howlers in this area?
.... and so on - these are just a few possible hurdles, but there are many more
At the end of the day if this is not your niche-market (presumably an accountant) then why bother? Yes it may be more fun than chargeable work but is that really a valid reason, and have you any concept of the possible work involved to properly maintain umpteen dispersed spreadsheets?
This is not to ignore the fact that there are some truly exceptional members of the profession who have a comprehensive grasp of both sides and produce impressive work, however, they are in the minority. Unfortunately the majority fall into the category of 'Excel jockey'
It's horses for courses
There are lots of good free products out there (and VT Cashbook is one mentioned above) and certainly, if it is just a straightforward cashbook that is being developed especially if it is a generic version, an existing product may well be a better and more cost effective solution.
But there can be many reasons for developing something - normally a combination of a need for very specific tailored output, unusual inputs, ease of use and, to some extent, familiarity. Cross platform usage can also be a reason.
Your points are well made but be careful not to overstate it. Certainly it needs planning together with a logical and methodical train of thought; something accountants should be rather good at. I'm not suggesting that is sufficient to go out and write an accounting program or Apple app but there can be some very simple, very effective models that most people could write with a degree of knowledge and common sense.
I have models I wrote in Lotus 20 years ago which are still recognisably the same and still in use with a client.
Anyway as you say it is fun and it's good to create something for once. And who says it's not chargeable. :-)