Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating part # tags using sequential numbering.
Very simply, my question is this...
I want to try to use M EX to create tags for the parts we make during the manufacturing process. The tag would have some standard information about the part on the tag... The problem being that I have a formula (that works) for creating the sequential next tag # (0001, 0002, 0003, 0004...etc). The problem is that we have hundreds of boxes and that it would take days/weeks to manually make a tag for each box. Now I've tried using copy/paste but the problem is that the formula constantly looks for information in the wrong cells and that means manually changing the tag # cell of each tag manually. (I have to change 4 different cell references per tag). Is there an easier way to do this? Each tag takes up a space the size of A1-G12. The tag # reference cell is G8. The formula I've been using is "=IF((G21-G7=1), G21+1, G21+2)" with the first tag ref # being 1000 and the second being 1001. The tag # always references the 2 tag # before it. Any help with this is greatly appreciated. Sincerely, Andrew M. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating part # tags using sequential numbering.
Instead of using excel as the form, you may want to use excel to hold the data,
but create the form in MSWord. Then you could use MSWord's mailmerge capability to print those labels. You may want to read some tips for mailmerge. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. If that doesn't appeal to you, I think I'd put the "master" form in A1:G12. Then put the next label in A13:G24. But make all the formulas point at the form in A1:G12. Only use absolute references for the fields that won't change--titles and that kind of thing. But for the tag numbers, you could use =G8+1 (and give it a custom format of: 0000 (to preserve those leading 0's)). Then you could have a macro that would copy that label (in A13:G24) to A25:G36 and A37:g48 and.... It could even insert page breaks every 3 or 4 (or whatever) labels. Option Explicit Sub testme() Dim RngToCopy As Range Dim HowMany As Long Dim HowManyPerPage As Long Dim iCtr As Long Dim wks As Worksheet Dim DestCell As Range Set wks = Worksheets("Sheet1") HowMany = 50 'while testing HowManyPerPage = 3 With wks Set RngToCopy = .Range("a13:g24") Set DestCell = .Range("a25") .ResetAllPageBreaks For iCtr = 1 To HowMany RngToCopy.Copy _ Destination:=DestCell Set DestCell = DestCell.Offset(RngToCopy.Rows.Count) If (iCtr + 2) Mod HowManyPerPage = 0 Then .HPageBreaks.Add Befo=DestCell End If Next iCtr End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Andrew M. wrote: Very simply, my question is this... I want to try to use M EX to create tags for the parts we make during the manufacturing process. The tag would have some standard information about the part on the tag... The problem being that I have a formula (that works) for creating the sequential next tag # (0001, 0002, 0003, 0004...etc). The problem is that we have hundreds of boxes and that it would take days/weeks to manually make a tag for each box. Now I've tried using copy/paste but the problem is that the formula constantly looks for information in the wrong cells and that means manually changing the tag # cell of each tag manually. (I have to change 4 different cell references per tag). Is there an easier way to do this? Each tag takes up a space the size of A1-G12. The tag # reference cell is G8. The formula I've been using is "=IF((G21-G7=1), G21+1, G21+2)" with the first tag ref # being 1000 and the second being 1001. The tag # always references the 2 tag # before it. Any help with this is greatly appreciated. Sincerely, Andrew M. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating part # tags using sequential numbering.
Thx for the input. One of my coworkers knows how to use mailmerge and I'm
going to figure out the macro and we'll be a joint effort for a common cause. We appreciate the quick response. Best Wishes, Andrew M. Tylok International |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating pattern area under part of an x-y chart | Charts and Charting in Excel | |||
Creating a list in sequential order | Excel Worksheet Functions | |||
Creating Machine shop part schedule using Excel | Excel Discussion (Misc queries) | |||
Transpose unique values in one column/mult. rows into a single row | Excel Worksheet Functions | |||
Part Number/Qty Consolidations | Excel Discussion (Misc queries) |