Forum Discussion
Edit sequence in excel formulas?
So I'm trying to create member ID badges for a club. Sheet 2 has the member ID, first name, and last name on one line, but each in its own column. Sheet 1, however, is 2 cells down merged over 5 columns for their first name, and another 2 down merged with 5 across for their last name, then the club logo in the bottom left corner and their ID number, followed by a barcode under that on the right. Since each badge is a total of like 7 rows and 5 columns (2 badges wide and like 10 badges/page), every formula I try seems to pull every 7th person from sheet 2. Is there a way to tell it my sequence skips 7 rows on the first sheet?
Badges were also requested to be double sided with the same info on each side, so for the first page, I made page two swap the columns of badges(so the same name will be on both sides with double sided printing) and literally plugged in =$G1 for the first name kinda thing.. is there an easier way to make it do what I want without manually needing to change it for each one? I feel like there has to be an easier way.
To skip every 7th row when pulling data from Sheet 2, you can modify your formulas to use the ROW() function. For example, if your first formula is in cell A2 on Sheet 1 and you want to pull data from row 2 on Sheet 2 and then skip every 7th row after that, you can use the following formula:
=INDEX(Sheet2!A:A,(ROW()-2)*7+2)This formula will start at row 2 on Sheet 2 (the +2 at the end), and then skip every 7th row after that. You can modify the "7" in the formula to skip a different number of rows.
To make it easier to create the double-sided badges, you can use the "&" operator to concatenate the first name and last name columns from Sheet 2 into one column. Then you can use the same formula for both the front and back sides of the badge. For example, if your first name is in column B on Sheet 2 and your last name is in column C, you can use the following formula to combine them:
=B2&" "&C2This will create a single column with the first and last name separated by a space. You can then use this column in your badge formulas instead of separate first name and last name columns.
I kind of understand (apologies for taking so long to reply, hadn't seen anyone replied), but it's the reverse of the first formula I think.. I need 7 rows on Sheet1 to pull from a single row on Sheet2 with a row or two in between. Some are merged to different sizes as well. So Sheet1 has Columns A-E & rows 1-7 are one badge A (pulling data from A2, B2, & C2 from Sheet2) , then Columns A-E, Rows 9-15 are Badge B (pulling data from A3, B3, & C3 from Sheet2). Then when I reach the bottom of that Column on the first page, I start Badge E in Columns G-K, Rows 1-7 again, (pulling data from A6, B6, & C6 from Sheet2), etc. I'm trying to provide enough information without sharing any sensitive information.
Problem with your second formula is it needs to be identical to the first page and I'm not sure how to apply a formula that does Page 1 one way, Page 2 another, then back to the first way again on repeat. Is that a possibility?
