Advancing formula

Hi
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? 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...
thanks
Steve

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))

Hi!

To increment the formula reference: (I assume you're copying DOWN, not
across)

=INDEX(Barker!Z$11:Z$100,(ROWS($1:1)-1)*7+1)

Adjust for the end of the range as needed.

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

Use Edit>Replace for that.

Biff

"spankydata" <[email protected]> wrote
in message news:[email protected]...
>
> Hi
> 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?
> 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...
> thanks
> Steve
>
>
> --
> spankydata
> ------------------------------------------------------------------------
> spankydata's Profile:
> http://www.excelforum.com/member.php...o&userid=37217
> View this thread: http://www.excelforum.com/showthread...hreadid=569315
>

thanks very much. What does index do in general terms?