Forum Discussion

BrattyBlonde's avatar
BrattyBlonde
Copper Contributor
Apr 24, 2023

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 colu...
excel
Formulas and Functions
Rodrigo_'s avatar
Rodrigo_
Iron Contributor
Apr 24, 2023

BrattyBlonde 

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&" "&C2

This 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.

  • BrattyBlonde's avatar
    BrattyBlonde
    Copper Contributor
    May 03, 2023

    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?

Resources