Copy/paste of formulas with CELL references updates Here\'s my IF statement: =IF

Question

Copy/paste of formulas with CELL references updates

Here's my IF statement:

=IF(BBStart!$J$1<>"",MID(BBStart!$J$1,FIND(" ",BBStart!$J$1)+1,FIND("[",BBStart!$J$1)-FIND(" ",BBStart!$J$1)-2))

What I want is to copy/paste into the same spreadsheet as the above IF statement is BUT I want the REFERENCE to "J1" (aka, $J$1) to go from "J1 to K1 then to L1 then to M1 etc.

I can change the J1's to K1's manually but I have about 100-150ish of these to change.

If there a quick way? Essentially, each copy paste referencing the FIRST row of one spreadsheet (the "BBStart!$J$1) is copied into a column in a new spreadsheet thereby becoming "BBStart!$K$1" etc.

Thanks!!

Explanation / Answer

^You can use:";

=IF(OFFSET(BBStart!$J$1,0,ROW()-ROW($A$1))<>"",MID(OFFSET(BBStart!$J$1,0,ROW()-ROW($A$1)),FIND(" ",OFFSET(BBStart!$J$1,0,ROW()-ROW($A$1)))+1,FIND("[",OFFSET(BBStart!$J$1,0,ROW()-ROW($A$1)))-FIND(" ",OFFSET(BBStart!$J$1,0,ROW()-ROW($A$1)))-2),"")

T%his assumes that the first formula is in row 1 of the target sheet; if it is in (for example) row 5, change $A$1 to $A$5 throughout the formula.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.