transpose table of data into individual parcel labels

[ajm]

I am sure i have seen it somewhere. I have a list of address details in a table. Each row is a different address: Name in Col A, st numbers and name in Col B, State in Col c, and ZIP/Post Code Col D. I have set up tiles for each address across the next page (to begin with). I will have to reformat this next page to get the grids onto a page efficiently for printing. Baby steps. How do i get each row of data (each address), into a label? The list of addresses is loooong so i want to be able to drag the labels across.

Say address table is on Sheet 1. and labels are on Sheet 2.

Name	Address1	Suburb	State	Post Code
Bill Johnson	51 Blue St	Niceville	Victoria	3133
Mary Mary	17 Garden Grove	Nursery	New South Wales	2000
Quite Contrary	14 Streety Street	Horrorland	Queensland	4364

Labels

Bill Johnson			Mary Mary			Quite Contrary
Address	51 Blue St		Address	17 Garden Grove		Address	14 Streety Street
Suburb	Niceville		Suburb	Nursery		Suburb	Horrorland
State	Victoria		State	New South Wales		State	Queensland
Post Code	3133		Post Code	2000		Post Code	4364

 

[James006]

Hi,
If you need to print Address Labels, see following:

How To Print Address Labels From Excel

You can print address labels by using a combination of Microsoft Excel and Word. Follow our helpful guide to find out how to print address labels from Excel.

www.pcguide.com

 

[ajm]

thanks James. I'm locked into an excel solution for this job.

 

[ajm]

have found Choose which works if i want to enter each of the array elements manually ie choose(1, a2, b2, c2,d2,e2). it doesn't work entering "a2:e2" AND its limited to 256 elements.
@Alex Blakenburg @Peter_SSs gents, any ideas?

 

[ajm]

tried Index. it works for the first label and then the second does something weird.

AT WALL

new stores stickers.xlsx
	G	H
1	AT WALL
Sheet1

Cell Formulas
Range	Formula
G1:H1	G1	=INDEX([Atlas.xlsx]Sheet1!$D$4:$D$104,COUNTA($A$1:F$1))

hmm, it shows up like this actually:

the count says that there are 3 items in Row A when there are only two. could have something to do with the merged cells that the name will go into?

 

[ajm]

very strange. if i take the same formula down to a blank area and use it, i get the unsmudged answer, even when pasting into a merged cell. it seems that even though the cells are merged, there is a value in each of the underlying cells. that is, for MERGED C1:D1, the formula result should only appear in C1. BUT... both C1 and D1 contain the answer. I've not seen this before with excel. will post a separate question on it to see if anyone else has encountered this before.

 

[ajm]

other question is at: merged cells: data appears in both underlying cells