Data Entry Sheet reality check

[GTR27]

Hello! I have been a long time lurker and have come across a problem that i'm not sure how to proceed, and was wondering if people can give me some advice.

I have to work with a materials entry sheet (excel), for the construction of new bathrooms, and how they organise that sheet, is not how it needs to be entered into the MRP (Manufacturing resource planner). I have made an approximate generic sheet for how it needs to be entered, using similar formatting etc to the original sheet, however it is difficult and time consuming to look at the original schedule, and enter the materials, qty and price into the new sheet - and it requires the person entering to have knowledge of the MRP and where parts/materials/labour need to be entered.

People that create these sheets (estimators) are traditional "building" guys/girls, not manufacturers so they dont have the understanding of how a manufacturing plant works (and is different to a construction site). They do not have access to the MRP software, so cannot enter the data directly.

Now compared to the guru's on this board, I would only say that my skills are basic - intermediate (I have mostly done data analysis etc, with some VBA/macros). I am willing to invest time into the sheet, and learn along the way, but i'm wondering if excel is the best tool, or a combination of excel/access is better or even just straight out access?

Some details for those without a construction background :-

The original sheet is broken up into

Base
Wall & Ceiling Frames (materials)
Linings/Carpentry
Electrical/Mechanical Services
Plumbing
Sanitary Fixtures & Tapware
Floor & Wall Tiling
Preparation & Delivery to site

The new sheet, that is to be reference to enter the data into the MRP is essentially setup as it would flow around the factory.

So for example

Workstation 1 + Materials / Labour
Workstation 2 + Materials / Labour
Workstation 3 + Materials / Labour
...
...
...
Workstation 50 + Materials / Labour


Now each of the workstations does a specific function/task, and it may have multiple trades work there, and the materials/labour required are workstation specific. Multiple workstations may do the work listed in the original sheet (i.e. the parts/materials/labour for linings is actually split across about 6 workstations).


My problem is I need to find an easier way to get the estimators a way to enter data, easy for the estimator to enter, then easy for someone with MRP access to enter it into the system for production, based on the fact that we will likely have to enter every single bathroom as a unique entity and we have a few thousand bathrooms, you can imagine how much time this would take doing things manually.

I can think of many ways of doing certain parts of the above task, but wanted a reality check from others to see if what i'm trying to do is achievable within excel or access? The MRP Looks after scheduling (i.e. we just enter materials/parts/labour and delivery date and it calculates it all, creates PO's etc) so we really just need the data to be able to be captured so the data entry person for the MRP doesnt have a heart attack!


My thoughts at the moment - create something in Access (so I can export database data from the MRP straight to access) and create a sheet/template for the estimators. Ideally, if they could use the current estimate excel sheet as a front end, correlate it with this "new" access database and spit out a sheet for data entry to the MRP (and down the track, automatic entry to the MRP once some software development was done by the MRP people) that would be my goal.

Happy to provide examples of the estimators sheet, and the sheet that resembles how the data needs to be entered to the MRP if people require that to think about the problem.

Note also, that my experience with access is somewhere between 0 and 0. I have a basic understanding of tables etc, but havent ever written an SQL query etc, so anything access would require some serious time investment on my behalf (I am willing to do this, because long term this makes my job infinitely easier)

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

It sounds to me as if you already have a perfectly good MRP system but you just need an easy way to get the data in there.

On that assumption, I don't see the advantage of using Access. If you needed to store the Materials Entry Sheets and be able to make adjustments to them for months or years after they were entered then there could be a use for Access but I guess that once they are processed they are not of much more use.

From what you have said, the site people are OK with Excel, you just need a "magic gearbox" that will transform the Excel data from them into the format required by the MRP system. If that is true, then you will need to standardise the Excel system to prevent people making any free-format changes then you will need to have some macros, in a central Excel workbook, that can read any new workbooks from the site operatives and process it into MRP requirements.

So, how about, Workbook1 with the processing/checking/reformatting macros, Workbook2 from the site operatives and a temporary Workbook3 that has the MRP input in it so that you can eyeball it prior to hitting the Load MRP button when the data in Worlbook3 will be fed into MRP?


At a later date, you could add more functionality the Workbook1 so that when the data is uploaded into MRP it also makes a copy of the data in Workbook2 in Access if it became necessary.

 

[GTR27]

Thanks for the reply rick. Yes, I pretty much just need an easy way for the estimators to fill out data, so that someone can enter it into the MRP. My problem at the moment in regards to that, is if it's a "new" build, they may specify materials or parts that are not currently in the MRP (and the person entering it will need to create a new material etc so that it matches).

I have to think about how I can get the data from how they enter, to a usable format. I'm considering making them use the sheet that I have made that is pretty much laid out how it would be entered in the MRP, then maybe run a macro that looks at the current materials list, and anything that does not have a part number, copy that material to another sheet, rename it "new parts" or the like so it is flagged and someone can add them before the data entry person attempts to update the MRP. (Assuming here data entry person has only the most basic knowledge of the MRP, they are literally just "keying" things in.

I think i'll go have a play with some vba for retrieving data from workbooks in a certain location, then filtering/formatting data to get the sheets to a workable state.

I guess the plan here is small steps, get part of the process automated (VBA) then work on improving it until it's a open and click procedure, so I can hand it over to someone and move onto the next exciting task! I have a lot of work ahead of me, as I said, have only really dabbled in VBA (and have done some C programming a long while ago) but so far, it looks reasonably straight forward to get a decent result. These forums certainly help!

Hopefully I can search and get a result for most of my smaller questions (i'll try and break each task i'm doing down into seperate steps and work on getting each step working).

I'm always open to hear what others have to say, as often they may look at it in ways I havent considered.

 

[RickXL]

If you have used C then the programming should be OK at the detail level.

What you will need to know is the Excel object model. Excel has some powerful objects, like worksheets, for instance, or columns or blocks of cells (ranges) etc that you don't find in C. You need to understand these to make the best use of Excel.

For instance, I saw a VBA program here recently that used VBA to sort a range of cells. Impressive , but you can just say Range.Sort in Excel to do the same thing.

 

[GTR27]

I'm also concurrently studying mechatronic engineering, so have had to program a teensy and nokia screen to play breakout and pong using C (Not C++ or C# etc) making my own libraries etc. Just have to get my head around how excel and VBA does it, so I can utilise it! Like I said, starting to dabble a bit now - I use macro recorder, do the things I want, then go into the editor and clean it up/make it more efficient. After doing that for a bit, i'm starting to learn what commands relate to in VBA.

Does anyone have a better way to learn? I have the MR Excel book, but havent had a chance to pick it up and do the examples.

 

[RickXL]

I've been into computers for some time now. Me and Alan Turing were alive at the same time!

I started with FORTRAN and Assembly language and many years later started using Excel for work. Not programming it but using it to manipulate data. So my original computer languages were heavy on algorithm but light on data structures. The recent breed of more object oriented languages have a lot of objects that you have to know your way around as well.

I usually describe the difference between my original Assembly language and VBA like this. Think of going on a journey. Assembly has instructions for things like: take a step forwards, another for turn left and another for stop. VBA on the other hand has a single instruction to travel between all the major airports of the world. Assembly can get you exactly where you want to be but is very painstaking while VBA will get you close in a single step, if only you knew the command, but you will need to find more commands to find your way to the city centre etc.

I find I can learn only so much from books and web sites then I need some real world examples. That is why I came here!

 

[GTR27]

I found with C especially that you really just have to get in there and try and do it. Spend hours breaking things down, then doing that part. Eventually you have enough parts to do what you want, then combine the functions together to get a working program.

Does anyone have a list of all the excel/VBA functions? (i.e. so you can go "i need to do this" look at the list, find a function and try it out)

EG Selection. and a list of all the "Selection" functions - etc etc.

 

[RickXL]

The easiest thing is probably to just use Google. For instance, search for: Excel VBA functions

Excel does have a HELP but these days it points you at the web anyway. Also, much documentation can be found on the Microsoft site. You could try Googling something like this:
excel vba functions site:microsoft.com

 

[GTR27]

Cheers! I just found out that if you open up the VBA editor, there is an object browser that show commands! (Thanks google!). Might do a bit of experimenting over the xmas break.