Re: OT - Are there any Microsoft Excel experts aro

How about a better example of what you're trying to get into a Macro?

 

Re: OT - Are there any Microsoft Excel experts aro

I work for a store fixture manufacturer. We have an Excel spreadsheet that is used as our cutbill.

All our parts are cut from sheet stock (plywood, particleboard, laminate). All these sheet stock items items have their own part numbers in our database.

All the hardware items have their own part numbers.

Let say you have a cabinet. Each line item (door, side panel, top, back, shelf) has a size to be cut out of a certain sheet good. Some times it's all the same material, most times it different (thicknesses or sheet size). Each line is set up to figure percentage of sheet goods used(sheet size / cut size).

The hardware is simply compiled, pull qty, hinge qty, shelf pin qty... etc.

After this cut bill is completed. We have to compile "like" stock items to come up with a total BOM (bill of material).

I'm wanting to create a macro to work it's down a column (item numbers from the database) totalling each of the sheet good numbers and hardware items into a list.

 

Re: OT - Are there any Microsoft Excel experts aro

You can do that with Excel but the problem becomes the interface and how you interact with it.......One easy way is to have a qty column...you put in a calculation that multiplies the qty by the size of the cut and puts that into a temp sum cell...then at the bottom or another sheet you have it build up the BOM based on the temp sums from the previous sheet.

I personally think going with an Access data base would be better....well, actually, if it was me...I would write a Delphi windows programt to do it for me....

It would have a couple of different forms...one to enter data...another to build the BOM....etc....

 

Re: OT - Are there any Microsoft Excel experts aro

I'd second using Access as a user interface and designing the report function to print out the BOM. Access is usually easier for most computer-challenged users to use.

 

Re: OT - Are there any Microsoft Excel experts aro

Ok... I think you guys are getting one step ahead of me.....

(please see attachment)

The Excel spreadsheet is a single sheet. We have it doing the "temp sum" already (column Q). Each line item does that part of the equation.

I'm not wanting to interface with our database... yet.

Right now we have a place at the bottom of the Excel sheet that lets us cut and paste the item numbers into a column (starting at I67). Once a number is typed into the proper box, Excel searches column "Q" above for this item, and totals up every occurance it finds. But it relies on us to make sure we type the right item numbers in the boxes. If we miss it, it doesn't get totaled. And if we put it twice it gets totaled twice (not a big issue). So I'm trying to take some of the human error out of things.

Currently we are "cutting and pasting" this compiled BOM into notepad, doing some editing, and THEN pasting into our company's database. It's not a great system. But it does work better than typing all the information manually. I'm just trying to automate it.

I don't mind learning Access if it'll ultimately make things smooth. But this cutbill isn't something that I can eliminate.

Getting the information into our database IS the next step. But our system seems VERY old... and I'm not sure what info you need to interface with it..... (Caldera??)....

I'm already pushing the limits of my geekness!

Thanks for all the suggestions!!

 

Re: OT - Are there any Microsoft Excel experts aro

The way I'm reading the description on this, the project isn't heavy duty enought to be placed in an Access DB. It appears to me that this is a per-job thing. So, say you have an Excel spreadsheet with the list of items you used to create the product (cabinet). You would want to sum up the amount of each item and total it in a separate area of that spreadsheet.

If you were to set up a ID for each item that you want to sum, you can setup a macro and a little vba to get it to cycle through that.

I'll see if I can throw something together for you tonight, I'm away on business, so I have nothing else better to do in the hotel after hours.

Sean

 

Re: OT - Are there any Microsoft Excel experts aro

You got it eeboater.

The "ID" is the column labeled "part no".

This is pretty much an engineering tool. The company has a seperate database that drives production and purchasing. I don't want to create a duplicate database. I just need the totals to appear at the bottom of the spreadsheet for each item. Then we'll cut and paste this information into notepad, edit, then into our database.

The next step would be to get this to export into our database. I imagine that's where the ACCESS would be used. But I'm really talking over my head on that one....

Thanks for the help!

 

Re: OT - Are there any Microsoft Excel experts aro

If your database is ODBC compliant (Open database connector) you can import info into a excel spreadsheet and export it out, usually with the touch of a button. However it requires a good DBA to figure out what goes where and to set up a good connection.

Delphi? Oh John pleeeeeze!

 

Re: OT - Are there any Microsoft Excel experts aro

I must be misunderstanding what you are asking considering the answers that you are getting.

Try this:

1. Click on some field, any field except the first one you want to fill in.
2. Click Tools - Macro - Record New Macro
3. give the macro a name and a shortcut key if you like
4. Click OK
5. Now click on the first field you want to fill in
6. TYPE in the part number that you want (cut and paste didn't work)
7. Move to the next field and repeat as needed
8. Close the create macro box
9. Clear everything you typed in
10. Run the macro and it should do what you did
11. Don't forget to clear everything before you start a new part or save

 

Re: OT - Are there any Microsoft Excel experts aro

Like Taz, I'm not really sure what it is you're trying to do with your spreadsheet, but as he said, just record what you do once and you can use that macro to do it again and again whenever you want. Just follow his instructions.
You can then use insert/picture/clipart and place a button into the spreadsheet. Once it's placed where you want it, right click on it and choose "assign macro". From here pick the macro that you just recorded, which is nothing more than a series of steps that you have just performed.
Now, all you have to do is open the sheet and click on the button, and whatever actions you performed while in "record macro" mode will be done automatically.
Make sense?

EDIT: I added a small crappy example of what I'm talking about.

 

Re: OT - Are there any Microsoft Excel experts aro

This sheet that I have attached is just a prototype sheet. It has one of each formula that we might use to create a fixture.

Typically the line that is labeled "top panel" would be inserted over and over again, until the fixture was complete. Of course we rename the part, change the description cell, change the cut size cells, check the "mat size" cells and the "part no" cell is adjusted to match our data base (a different system altogether).

When the fixture is complete, the spreadsheet can be one line item. Or perhaps as many as 100 line items long (slight exaggeration). But every part to the fixture (or cabinet if you prefer simplicity) has it's own line item.

Down at the bottom of the sheet outside of the print area, we keep a tally of the "part no" cells' total. So if we used plywood that is part no #2123 in ten line items. At the bottom of the sheet we'd have a total quantity of all ten line items that used that grade/size of plywood (#2123).

The problem with Taz's suggestion is that I don't know what I want excell to look for. Each cutbill is different, and we constantly generate new numbers.

Excel needs to work it's way down column "P". When it runs into a part no, it needs to sum the quantity associated with all the occurances of that part number. Then Excel needs to continue onto the next part number in the column and do the same. Until all the part numbers in column "P" have a total at the bottom of the sheet.

I will try the creating a macro tomorrow. I'm just not sure how to get Excel to recognize that I'm scanning a column looking for like numbers.

 

Re: OT - Are there any Microsoft Excel experts aro

Look at this attachment. I made a cabinet. You can see each of the cabinet's parts has a line item. The "part no" column is adjusted to show the sheet goods' number. Parts 1,2,and 4 are all made from the same raw part number. Parts 3 and 5 are made from something different.

Now down at the bottom of the page begining with cell I78, I manually entered the part number #5451, and the formula in the spreadsheet totaled all the occurances of #5451 in column "P" (part no).

This is how our spread sheet works now. But I have to manually enter these part numbers. It is very tedious to work down a LONG list of part numbers and be sure that you don't miss, or duplicate a part number.

I was hoping to create a macro, search column "P" for a number, find all "like" occurances, then sum, enter number and quantity down below spreadsheet. Search column "P" for next number, find all "like" numbers, and sum, enter number and quantity down below spreadsheet. Repeat until a summarized bill of materials is created.

Keep in mind that every spreadsheet will have different numbers in column "P".

Is that clear as mud?

 

Re: OT - Are there any Microsoft Excel experts aro

The "K" column down below is already doing the seach and sum bit. But it relying on me to manually give it a number to look for. I was hoping a macro would automate that number generation.

 

Re: OT - Are there any Microsoft Excel experts aro

OK, I think I MIGHT understand what you're trying to do now.
I think that what you'll need to do is use a pivot table, or at least that's how I'd go about it. But, you'll need to make a few simple changes to your spreadsheet first to get it to sum out right. Well, I had to in order to make it work. I had to put the qty column next to the part# column. There might be a way around this, but not in my feable mind.
Look at the attachment and see if that's basically what you're wanting, or at least close. I made the part# and QTY columns I used blue and bold so you can see them easier in case they are wrong.
It should at least be close enough to make the point.