Forum Discussion
Create a Formula to pull Data from 1 row, 3 columns on Sheet2 into multiple Rows and columns Sheet1?
I'm making badges for a club and each badge on Sheet1 is 5 columns by 7 rows, with one row and column in between each badge. When I try to make the formula autofill, It grabs every 8th or 9th name on the list from Sheet2. Is there a way to tell it all 7 rows on sheet1 correspond to just 1 row on Sheet2 and needs to grab each one? I've attached a dummy sheet to illustrate my point. We have over 1000 members I need to do, so an auto working formula would be a lifesaver.
Badges are also to be double sided, same name on both sides, so I've made page 2 of sheet one swap the columns and read the info from page one, but if it could automatically do it for me (every other page) it would save so much time.
As long as there is consistency in the row heights and row counts per badge, this can be done. See the attached workbook.
As I note on the _Info worksheet, the key concept is to use the ROW() function in each formula to let it figure out which row in Sheet2 should supply the data for that cell.
Blank rows on Sheet2 cause badges with zeroes for cell content. Those could be suppressed, but why bother? The formulas would be even more complex. And I would expect blank rows normally to appear only at the end of the Sheet2 list.
You've had over 80 views and no replies. I suspect it's partly due to the difficulty giving a "simple" answer in Excel. So I'm going to be bold and make a suggestion, that being a simple answer that uses some of what you've done, but goes in a different direction,
If I were doing this, I'd use your Excel list (Sheet2) as the source file, but use Mail Merge in Microsoft Word for the output. You'd have much more control of the layout of the badges themselves in Word, and the matter you're concerned about--getting each name in sequence--would be handled very easily; that's how MailMerge works! Here are some YouTube videos that explain how to do exactly that, using Excel as the source, Word for output.
The second task you describe (printing on both sides) would be accomplished by just running the routine twice, having the badges down the center of the page so they line up on each side.
- Mail merge might work, but to make them double sided, seems like a waste of paper since it'll have to use twice as much. And that's also assuming the printer wouldn't grab any pages out of order, which I've seen printers do plenty of times.. Thanks for the idea, but I think I'm still gonna try to make excel work..
