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


B

BoyGenius

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! D2:D5 )+MIN(sheet! D2:D5 )+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)
 
Ad

Advertisements

S

Sheeloo

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

BoyGenius

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

Advertisements

S

Sheeloo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top