Copy/paste of formulas with CELL references updates Here\'s my IF statement: =IF
ID: 3561150 • Letter: C
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.