Function question
 

I have external sheet references in a worksheet and I
want to copy the formula's, but can't figure out how to
do it. Can you help?

Column A
='healthcare'!c9
='healthcare'!c14
='helatchare'!c19
='healthcare'!c24

and so on....

What function of Excel can I use to copy the formula down
and skipping every 5 cell references? Is it possible? I
have tried to "Fill" it, but with no success.

Entered in A1 on the destination sheet & copied down:

=INDEX(healtcare!$C$9:$C$90,(ROW()-ROW($A$1))*5+1)

Adjust to suit.

cindi wrote:
I have external sheet references in a worksheet and I
want to copy the formula's, but can't figure out how to
do it. Can you help?

Column A
='healthcare'!c9
='healthcare'!c14
='helatchare'!c19
='healthcare'!c24

and so on....

What function of Excel can I use to copy the formula down
and skipping every 5 cell references? Is it possible? I
have tried to "Fill" it, but with no success.

One way

Try in any starting cell:

=INDIRECT("'health care'!C"&ROWS($A$1:A1)*5-5+9)

(above returns ='healthcare'!c9)

Copying down will return in successive cells:

='healthcare'!c14
='helatchare'!c19
='healthcare'!c24

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
cindi wrote in message
...
I have external sheet references in a worksheet and I
want to copy the formula's, but can't figure out how to
do it. Can you help?

Column A
='healthcare'!c9
='healthcare'!c14
='helatchare'!c19
='healthcare'!c24

and so on....

What function of Excel can I use to copy the formula down
and skipping every 5 cell references? Is it possible? I
have tried to "Fill" it, but with no success.

What function of Excel can I use to copy the formula down
and skipping every 5 cell references? Is it possible?

One way ; do the first two manually (='healthcare'!c9, ='healthcare'!c14),
then do a Replace (CTRL+H) of "=" with "x=". Then select *both* and drag
down (click & drag the small black square ("fill handle") at bottom right
corner of selection).

When you're down as you need, do Replace again but this time replace "x="
with "=".

Rgds,
Andy