Copying a list without spaces to cells with spaces between them.

Hi all, this is my first post here, and I have looked around the forum for an answer to my problem but to be honest, I dont even know the correct terminology for what I'm trying to do.

Basically, I have been tasked with assigning equipment to people on a roster, and also creating a printable card for them to turn in and have the equipment issued. The cards are about 13 cells tall, with 1 cell spacing between them. I can get roughly 3 to a page.

What I need to do is pull various data from the roster sheet and put it in cells on these little cards. I can do the first one just fine, but if I try to do the second one, either below or next to the first one, it will not properly index to the second row on the roster sheet. It reaches straight across and pulls data from row 15-ish. I have spent hours on youtube, various how-to sites, and here trying to figure out how to do this with no luck

Basically what I am trying to do is pull data from Sheet1.A1, B1, C1, D1 over to Sheet2.A1, B1, C1, D1, Then pull data from Sheet1.A2, B2, C2, D2 over to Sheet2.A15, B15, C15, D15, roughly 300 times. I have looked at INDEX/MATCH combinations but I cannot figure out how to do this. If this is unclear please let me know, I can try to explain it better.

Any help on this would be greatly appreciated. Thanks in advance!

I attached an example workbook so you can see what I'm trying to do. I made 2 cards, the top one is correct and the second one I highlighted in red what I want to put in those boxes. I can manually code them no problem, but doing so 300+ times on my real workbook will be unmanageable. I hope it makes sense what I'm trying to do

For the one in cell A26 (use a similar structure for the other cells)...

='EQUIPMENT LIST'!B2

yes i can manually enter where the data is supposed to come from, but doing so 300+ times is going to be a nightmare. I was hoping there would be a way to formulate one, then drag it down and make more. If not, that's a bummer, but I guess I'll make do.

Originally Posted by NoviceRenegade

yes i can manually enter where the data is supposed to come from, but doing so 300+ times is going to be a nightmare. I was hoping there would be a way to formulate one, then drag it down and make more. If not, that's a bummer, but I guess I'll make do.

You showed one "card" with three cells you needed formula in... are you saying you have 299 more of these "cards" somewhere? If so, where? Maybe if you showed 2 or 3 or them in their proper location, we might be able to figure something out for you, but showing us one range and making us guess where the other ranges might be isn't going to get you far. I will say, though, that I don't think a formula solution it possible because it looks like you might have constant data between the cells requiring the formula (I'll know for sure when you show us addition ranges with those red cells that need the formula). If that is the case, there might be a macro solution available, but again, I will need to see more of your layout to know for sure.

No problem Rick, sorry for the confusion. I have added 4 more, with the 5th one showing the error I'm struggling with in red. The first 4 cards are manually entered, but when you copy/paste the template, the formula in the cell reaches horizontally across to the list sheet, as opposed to incrementing by 1 row from the card above it. I don't know how to increment the data by just 1 row per card. Hope that helps.

I have some experience in regular programming, so my train of thought was something along the lines of

Card 2 cell reference = Card 1 cell reference + 1. If I could get that working, I could manually code the first card, then use said formula on 2-300 and it would be easy peasy. I just don't know how to make excel do that, or if that functionality even exists. I hope that makes sense.

Try this:

1.Number:

=INDEX('EQUIPMENT LIST'!A:A,COUNTIF($A$1:$A1,$A1)+1)

2.Name:

=INDEX('EQUIPMENT LIST'!B:B,COUNTIF($A$1:$A1,$A1)+1)

3.SERIAL #:

=INDEX('EQUIPMENT LIST'!C:C,COUNTIF($A$1:$A1,$A1)+1)