Advancing formula
spankydata wrote...
When I write a formula for a cell and the click and drag to adjacent
cells, excel advances the formula in a certain manner. I am using
person's names. For example =Barker!Z11 becomes =Barker!Z12 in the next
cell. Is there anyway of making excel advance the formula in a certain
way? In this case I want it to advance by 7 so the formula for adjacent
cells reads =Barker!Z18 and =Barker!Z25 and then =Barker!Z32 and so on?
Not using simple cell references. Excel provides only 1-to-1
correspondence, meaning fill formulas +/-1 row/column over, and cell
addresses adjust +/-1 row/column.
You could use INDEX. If the first formula were in cell X99,
X99:
=INDEX(Barker!Z:Z,11+7*ROWS(X$99:X99))
The 2nd argument evaluates to 18. Fill it down one row, and that INDEX
call's 2nd argument evaluates to 25.
Also can you rename part of a formula across a range of cells so that
Barker is replaced by Ball. In other words =Barker!Z11 becomes
=Ball!Z11 etc...
No, but if you're in a cell from which you want to refer to Barker!Z11,
you could define the name Ball referring to =Barker!Z:Z, then change
the INDEX formula above to
X99:
=INDEX(Ball,11+7*ROWS(X$99:X99))
|