We have a client...........
who has a serious chart of accounts, which will expand in the future.
Instead of using their in-house DMS system, they are considering exporting a TB of 11500 lines at the end of each month and importing the COA into a Management Accounts Structure. Whilst I have written MA's in the past, it is now 10 years ago, so could anyone pass on their Excel Expert details (must be able to do Micros); and it must be bomb proof before I release it to the client. Hopefully we could work together on a collaboration basis.
Kind regards,
Stephen
Replies (12)
Please login or register to join the discussion.
How about
https://www.accountingweb.co.uk/user/153640
and take a look at his blog
https://www.accountingweb.co.uk/blogs/gfeechan/not-just-numbers-highlights
Do you mean VBA(Macro)?
If it is a simple job, you can get away with freebies from on-line community. There are hundreds of so called "experts" who are ready to sell their product. You should be able to find them by goggling "Excel expert".
Peter Clayton
Used him a number of times for excel work. Some of the things he's done have not been complicated, but his rates are reasonable and it has come down to the fact it's cheaper to pay him than it is for me to try to work it out myself!
What's My Line?
You'll need someone who inter alia can find a workaround for the Excel maximum limit of 10,000 lines.
Filter, although I'm using pre-2010 Excel
It's the filter limit - 10,000 lines maximum. However, I've just googled and it seems there's a workaround in Excel 2010 and onwards by using the "custom" filter in order to exceed that 10,000 lines limit. Guess I'm a little behind the times with that. So false alarm, sorry.
Always limits with visible cells
Yes, the visible cells in filter have limits, but this is not new. It has been with all versions of excel. The limit has gone up to something 20k in 2010.
Workaround is to sort the range before you apply the filter hoping that the visible cells do not exceed the limit. If you want more information, you can visit Ron Deburin's site. I am unable to attach the website link.
Why macros?
Not sure of the need for macros. In my experience they tend to cause more problems than they fix.
With similar numbers of codes I've used pivot tables to sort into meaningful categories (assuming there is meaning in the accounts code - surely there must be with that many!) which then push into a static TB which drives the reporting. Then your accounts production consists of a download, a refresh of the pivot table and job done.
Code and Marcos are almost the same
Not sure of the need for macros. In my experience they tend to cause more problems than they fix.
With similar numbers of codes I've used pivot tables to sort into meaningful categories (assuming there is meaning in the accounts code - surely there must be with that many!) which then push into a static TB which drives the reporting. Then your accounts production consists of a download, a refresh of the pivot table and job done.
Hmm!
I thought you said "Not sure of the need for macros"
I used the words "macros and "VBA code" as the same, but strictly, they are not.
Do not use a code
Uther,
The methods and properties of Excel's filter function are the same as a macro. In fact, you can record a macro and get the same result as you do it manually, so the issue with visible cells will not go away if I were to write a code.
Alan,
It is debatable if and when to use a code. It all depends on person test. My view is: Do not use a code if you do not understand what the code is supposed to do.
Cantona
I think you are misunderstanding me. I used the word code as in account code not as in VBA code.
To the OP:
To illustrate further where am I working currently has an account code string of xx-yyy-zzzzz-aaa x = location, y = cost centre, z = nominal code, a = project code. Each combination of these codes lead to another line on the TB, however for company wide management accounts only z's are relevant so a pivot summarising those is only around 400 lines long. If you need reports by location/cost centre/project then all the info is there and you can run pivots from the same data to populate with a simple 'refresh all' click to update.
To be fair if you need anything more serious than that then a report writing package to sit along side their finance system would probably be far cheaper and less hassle in the long term.