If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
how do I create a nonadjacent series for autofill to go by?
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) |
#3
|
|||
|
|||
how do I create a nonadjacent series for autofill to go by?
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) |
#4
|
|||
|
|||
how do I create a nonadjacent series for autofill to go by?
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) |
Thread Tools | |
Display Modes | |
|
|