how do I create a nonadjacent series for autofill to go by?

My goal is to create formulas in each cell in series so I do not have to fill
in each cells formula by hand. (would be very time consuming) The series of
references are not adjacent to each other but they do follow a specific
pattern. How can I use autofill, or some other usefull method, to finish my
worksheet?

Example:
A1=IF(sheet1! C2 =0,NA(),(MAX(sheet1! B2:B5 )+MIN(sheet1! B2:B5 )+sheet2! B2 )
B2=IF(sheet1! E2 =0,NA(), (MAX(sheet1! D25 )+MIN(sheet! D25 )+sheet2! C2 )
C2=IF(sheet1! G2 =0,NA(),(MAX(sheet1! F2:F5 )+MIN(sheet1! F2:F5 )+sheet2! D2)

(As it is the autofill will only give a series of which I can not use)

Use INDIRECT in place of each reference and pass a formula to it which
results in the reference you want.
For example instead of
=SUM(A1:A10)
use
=SUM(INDIRECT("A1:A" & C1))
with 10 in C1...

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"BoyGenius" wrote:

My goal is to create formulas in each cell in series so I do not have to fill
in each cells formula by hand. (would be very time consuming) The series of
references are not adjacent to each other but they do follow a specific
pattern. How can I use autofill, or some other usefull method, to finish my
worksheet?

Example:
A1=IF(sheet1! C2 =0,NA(),(MAX(sheet1! B2:B5 )+MIN(sheet1! B2:B5 )+sheet2! B2 )
B2=IF(sheet1! E2 =0,NA(), (MAX(sheet1! D25 )+MIN(sheet! D25 )+sheet2! C2 )
C2=IF(sheet1! G2 =0,NA(),(MAX(sheet1! F2:F5 )+MIN(sheet1! F2:F5 )+sheet2! D2)

(As it is the autofill will only give a series of which I can not use)

i appreciate the comment, but I don't know how it would help run a series of
formulas across the worksheet. as you can see the formula i am trying to use
is more complex than a simple SUM of A1 and C1.

"Sheeloo" wrote:

Use INDIRECT in place of each reference and pass a formula to it which
results in the reference you want.
For example instead of
=SUM(A1:A10)
use
=SUM(INDIRECT("A1:A" & C1))
with 10 in C1...

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"BoyGenius" wrote:

My goal is to create formulas in each cell in series so I do not have to fill
in each cells formula by hand. (would be very time consuming) The series of
references are not adjacent to each other but they do follow a specific
pattern. How can I use autofill, or some other usefull method, to finish my
worksheet?

Example:
A1=IF(sheet1! C2 =0,NA(),(MAX(sheet1! B2:B5 )+MIN(sheet1! B2:B5 )+sheet2! B2 )
B2=IF(sheet1! E2 =0,NA(), (MAX(sheet1! D25 )+MIN(sheet! D25 )+sheet2! C2 )
C2=IF(sheet1! G2 =0,NA(),(MAX(sheet1! F2:F5 )+MIN(sheet1! F2:F5 )+sheet2! D2)

(As it is the autofill will only give a series of which I can not use)

Enter this in A1
="sheet1!"&CHAR(66+(ROW()-1)*2)&"2:"&CHAR(66+(ROW()-1)*2)&"5"
and copy down...

Idea is to build references you want using strings and references and wrap
them in INDIRECT...

INDIRECT supports R1C1 reference style which is easier to build

"BoyGenius" wrote:

i appreciate the comment, but I don't know how it would help run a series of
formulas across the worksheet. as you can see the formula i am trying to use
is more complex than a simple SUM of A1 and C1.

"Sheeloo" wrote:

Use INDIRECT in place of each reference and pass a formula to it which
results in the reference you want.
For example instead of
=SUM(A1:A10)
use
=SUM(INDIRECT("A1:A" & C1))
with 10 in C1...

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"BoyGenius" wrote:

My goal is to create formulas in each cell in series so I do not have to fill
in each cells formula by hand. (would be very time consuming) The series of
references are not adjacent to each other but they do follow a specific
pattern. How can I use autofill, or some other usefull method, to finish my
worksheet?

Example:
A1=IF(sheet1! C2 =0,NA(),(MAX(sheet1! B2:B5 )+MIN(sheet1! B2:B5 )+sheet2! B2 )
B2=IF(sheet1! E2 =0,NA(), (MAX(sheet1! D25 )+MIN(sheet! D25 )+sheet2! C2 )
C2=IF(sheet1! G2 =0,NA(),(MAX(sheet1! F2:F5 )+MIN(sheet1! F2:F5 )+sheet2! D2)

(As it is the autofill will only give a series of which I can not use)