ERP in MS Excel needs to be upgraded to something better

[raviarao68]

Hello fellow excel enthusiasts,
This is my first post as I am a new joiner - although i am now wondering what took me so long...!
I have been playing around with excel for the last 20 odd years and self taught myself whatever I needed to make it work.
I have developed and implemented a pretty intensive MS Excel ERP for my own Manufacturing small business enterprise.
It consists of storing information in various workbooks ( Data ) and using another set of workbooks with formulas to manipulate the Data to yield results and calculations. Over the years, it has done a wonderful job although one has to take certain precautions during its use.I have even invested in a good enterprise grade Server with multiple SSD's and huge RAM (256 GB ) with nComputing thin clients, to support about 10 users. Even then the files are slow and I am beginning to feel that the time has come to move to something better.
But I am loathe to get a custom ERP built since that would mean giving up on the ' Freedom ' that our entire team experiences in using Excel as their daily tool, which they can customize at will and trouble shoot themselves !
However, we are thinking that we may learn Access on our own and try to improve the existing system - I need some ideas on the above from the knowledgeable community out here - Can any of you suggest something to help us move on ?
I am also wondering how can one make all the ' Outputs ' like Deliver Challans, Invoices, Test Certificates, - Access cant make them...
How can we get results which we just use a simple Filter to get now...?
I have made maximum use of the VLOOKUP function in my ERP - that gives so many results if all the tables are structured properly - I would be lost in a new environment...
Please throw any ideas that you feel may be useful !
Thanks in Advance !
Will be happy to provide more clarifications if needed.

 

[ranman256]

I had a macro to take the excel list,then writes to a ERP text file.
is that what you want?

(I no longer have it, but can recreate.)

 

[raviarao68]

Hi, I am not sure I understand what you are trying to convey. What I am looking for is an alternative to our Excel package ....whether moving the data to access will give us a better way of getting our work done. If yes, what is the front end to use...as I have heard that access does not allow us to manipulate the data with formulas , etc. In Excel, we can do everything at once, we can store data in a workbook and put formulas in another and generate a delivery challan, or an invoice or any output report that we may need. Hope I am a bit clearer than earlier...

 

[ranman256]

Access DOES do formulas. They are in queries ,not in the table (ala Excel)
it is a great product for data organizing and export.

 

[xenou]

Designed an ERP in MSAccess would be a major undertaking. I would advise choosing a small part of it as a test case - first of all, you need to beginning learning SQL and fundamental database design theory. You will be better placed to decide after that if you want to take on the larger project or not.

To answer the original question, I don't know of any easy way to convert Excel projects to Access - it would be a major rebuild from scratch. An exception might be unless you are able continue to use parts of the current system by using it with Access storage for the data but Excel for the rest (even there, probably will involve some serious retooling). Also the new system will need an Access expert to maintain and enhance the data.