[aaron]

I will do my best to spell out the issue I'm having. I have an excel sheet
with 5 columns of data. I am creating "tickets" about 1" x 2.5" with this
data. Each ticket uses one record of data from the excel sheet. I want the
ticket formatted in such a way where the first 3 columns of data stack on top
of the last two. I am able to use the "label" utility to do this. My first
question is.

Does anyone know any tricks to do this so that I don't have to use labels?
I tried in excel to use cell references, but when I fill down, I can't get
Excel to logically "understand" what I'm trying to do. (i.e. =A2 then =B2
then =C2, then one row under that I tried =D2 then =E2)

My other question is regardless of whether I use labels or columns, can word
work all the way down to the last sheet needed and then across? Labels want
to work right, and continue to fill. Columns work to the bottom of one sheet
(snaking columns) and to the nest column, etc until it's done with the one
sheet. Because of how I have to "divvy" out these tickets, my procedure
would be much easier if I Word calculated the number of pages necessary and
filled one entire column (i.e. from sheets 1-5) and then returned all the way
to the top and filled down etc.

Any help would be greatly appreciated.

Thanks,
Aaron

[Suzanne S. Barnhill]

It sounds as if you're trying to reinvent the mail merge. See
http://word.mvps.org/FAQs/MailMerge/...AMailMerge.htm

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
http://word.mvps.org

"Aaron" wrote in message
...
I will do my best to spell out the issue I'm having. I have an excel sheet
with 5 columns of data. I am creating "tickets" about 1" x 2.5" with this
data. Each ticket uses one record of data from the excel sheet. I want
the
ticket formatted in such a way where the first 3 columns of data stack on
top
of the last two. I am able to use the "label" utility to do this. My
first
question is.

Does anyone know any tricks to do this so that I don't have to use labels?
I tried in excel to use cell references, but when I fill down, I can't get
Excel to logically "understand" what I'm trying to do. (i.e. =A2 then
=B2
then =C2, then one row under that I tried =D2 then =E2)

My other question is regardless of whether I use labels or columns, can
word
work all the way down to the last sheet needed and then across? Labels
want
to work right, and continue to fill. Columns work to the bottom of one
sheet
(snaking columns) and to the nest column, etc until it's done with the one
sheet. Because of how I have to "divvy" out these tickets, my procedure
would be much easier if I Word calculated the number of pages necessary
and
filled one entire column (i.e. from sheets 1-5) and then returned all the
way
to the top and filled down etc.

Any help would be greatly appreciated.

Thanks,
Aaron

[Suzanne S. Barnhill]

It sounds as if you're trying to reinvent the mail merge. See
http://word.mvps.org/FAQs/MailMerge/...AMailMerge.htm

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
http://word.mvps.org

"Aaron" wrote in message
...
I will do my best to spell out the issue I'm having. I have an excel sheet
with 5 columns of data. I am creating "tickets" about 1" x 2.5" with this
data. Each ticket uses one record of data from the excel sheet. I want
the
ticket formatted in such a way where the first 3 columns of data stack on
top
of the last two. I am able to use the "label" utility to do this. My
first
question is.

Does anyone know any tricks to do this so that I don't have to use labels?
I tried in excel to use cell references, but when I fill down, I can't get
Excel to logically "understand" what I'm trying to do. (i.e. =A2 then
=B2
then =C2, then one row under that I tried =D2 then =E2)

My other question is regardless of whether I use labels or columns, can
word
work all the way down to the last sheet needed and then across? Labels
want
to work right, and continue to fill. Columns work to the bottom of one
sheet
(snaking columns) and to the nest column, etc until it's done with the one
sheet. Because of how I have to "divvy" out these tickets, my procedure
would be much easier if I Word calculated the number of pages necessary
and
filled one entire column (i.e. from sheets 1-5) and then returned all the
way
to the top and filled down etc.

Any help would be greatly appreciated.

Thanks,
Aaron

[Graham Mayor]

See http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
or possibly the add-in for numbering labels
http://www.gmayor.com/Numbered_labels.htm

Labels are merely tables, but as you appreciate they fill from left to
right.

The label numbering add-in will number down the column if preferred.
Alternatively you could re-sort your data to provide the correct order when
merged. To effect that copy the Excel table to a Word table and run the
following macro, then use the resulting file to merge your data.

Sub LabelsUpAndDownSortData()
' Macro to assign numbers to data source so that it can be sorted to cause
'labels to print down columns
Dim Message As String, Title As String, Default As Integer
Dim labelrows As Integer, labelcolumns As Integer
Dim i As Integer, j As Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = 3 ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "8" ' Set default.
labelrows = InputBox(Message, Title, Default)
With ActiveDocument.Tables(1)
..Columns.Add BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
..Rows(1).Range.Cut
End With
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore _
k + (j - 1) * labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + _
labelcolumns * labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Aaron" wrote in message
...
I will do my best to spell out the issue I'm having. I have an excel sheet
with 5 columns of data. I am creating "tickets" about 1" x 2.5" with this
data. Each ticket uses one record of data from the excel sheet. I want
the
ticket formatted in such a way where the first 3 columns of data stack on
top
of the last two. I am able to use the "label" utility to do this. My
first
question is.

Does anyone know any tricks to do this so that I don't have to use labels?
I tried in excel to use cell references, but when I fill down, I can't get
Excel to logically "understand" what I'm trying to do. (i.e. =A2 then
=B2
then =C2, then one row under that I tried =D2 then =E2)

My other question is regardless of whether I use labels or columns, can
word
work all the way down to the last sheet needed and then across? Labels
want
to work right, and continue to fill. Columns work to the bottom of one
sheet
(snaking columns) and to the nest column, etc until it's done with the one
sheet. Because of how I have to "divvy" out these tickets, my procedure
would be much easier if I Word calculated the number of pages necessary
and
filled one entire column (i.e. from sheets 1-5) and then returned all the
way
to the top and filled down etc.

Any help would be greatly appreciated.

Thanks,
Aaron

[Graham Mayor]

See http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
or possibly the add-in for numbering labels
http://www.gmayor.com/Numbered_labels.htm

Labels are merely tables, but as you appreciate they fill from left to
right.

The label numbering add-in will number down the column if preferred.
Alternatively you could re-sort your data to provide the correct order when
merged. To effect that copy the Excel table to a Word table and run the
following macro, then use the resulting file to merge your data.

Sub LabelsUpAndDownSortData()
' Macro to assign numbers to data source so that it can be sorted to cause
'labels to print down columns
Dim Message As String, Title As String, Default As Integer
Dim labelrows As Integer, labelcolumns As Integer
Dim i As Integer, j As Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = 3 ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "8" ' Set default.
labelrows = InputBox(Message, Title, Default)
With ActiveDocument.Tables(1)
..Columns.Add BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
..Rows(1).Range.Cut
End With
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore _
k + (j - 1) * labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + _
labelcolumns * labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Aaron" wrote in message
...
I will do my best to spell out the issue I'm having. I have an excel sheet
with 5 columns of data. I am creating "tickets" about 1" x 2.5" with this
data. Each ticket uses one record of data from the excel sheet. I want
the
ticket formatted in such a way where the first 3 columns of data stack on
top
of the last two. I am able to use the "label" utility to do this. My
first
question is.

Does anyone know any tricks to do this so that I don't have to use labels?
I tried in excel to use cell references, but when I fill down, I can't get
Excel to logically "understand" what I'm trying to do. (i.e. =A2 then
=B2
then =C2, then one row under that I tried =D2 then =E2)

My other question is regardless of whether I use labels or columns, can
word
work all the way down to the last sheet needed and then across? Labels
want
to work right, and continue to fill. Columns work to the bottom of one
sheet
(snaking columns) and to the nest column, etc until it's done with the one
sheet. Because of how I have to "divvy" out these tickets, my procedure
would be much easier if I Word calculated the number of pages necessary
and
filled one entire column (i.e. from sheets 1-5) and then returned all the
way
to the top and filled down etc.

Any help would be greatly appreciated.

Thanks,
Aaron