HELP: Transpose Issues

[JBSmithee]

Hello helper!

I am working on a sheet that is formatted like that attached one, this sheet has almost 1 thousand rows in it and I need to transfer these into a sticker format (Sheet2). I am trying to do this using Transpose, but the row:col keeps jumping by a factor of 10 as I copy down.

I enter the formula {=TRANSPOSE(B2:J2)} fill in the rows up to the space between stickers then highlight them and press ctrl shift enter to make it an array. It works for the first one, but the proceeding stickers are B12:J12, B22:J22, not B3:J4, B5:J5.

Please help me.

[newdoverman]

You can't use the transpose the way that you want as the destination has to be the same "cell dimension" as the source. In sheet2 you need separate transpose equations for each manufacturer.

On sheet3 I have included a transposition of the data on sheet1 using the transpose function.

[JBSmithee]

Thank you for the quick reply.

Now that I know I cannot use transpose, what shall I use in its stead? Filling in the individual forms losses a lot of the utility of the equations, doesn't it?

A push in the right direction would be helpful. I have tried the INDEX-MATCH method, but I cannot figure it out, the "lookup value" throws me off because I don't need to find a specific value, just copy them.

[newdoverman]

If you are actually creating stickers, then you can use your data as it is and do a mail merge in Word using the appropriate sized sticker. Other than that, I don't see the point in creating the arrangement that you are trying to create.

[JBSmithee]

I am actually creating stickers, without going into to much detail as to what my motives are for this sheet, I am looking for the format I posted, so that as data changes on sheet1 I can continue to add to the sticker forms on sheet2. I have over 500 line-items that I need to do this with. I am not sure what you mean by just do a mail merge, since I have never done that nor do I know how to add this particular template to it (5 stickers per sheet).

Even if the point is lost to you, it isn't to me. I am looking for assistance in this issue.

[newdoverman]

If you are using commercially available labels, then it might be worth the time and effort to see how Word can use an Excel table to create labels. If you have Word, it comes with many labels, and probably the one that you want to print on, pre-programmed and all you have to do is to enter the text that will appear on every label and the Field Headings, which are the column headings in Excel. Word then takes each record (one row of your data) and fills in each label as required.

Once you get on to how it is done, it is a lot easier than printing labels with Excel.

The only other option as I see it, is to have a macro fill in each of your labels on sheet2, select the print area then print.

Good Luck.