increase column b12 rows

[shamsu203]

Hi
in this formula
IF(OR($A$1=$A$6,$A$1=$A$10),Data!$J$15,0) when i copy the formula across i wnat to increase the data j15 by 12 to show j27 . the formula starts from b6. i am just making the worksheet as do not have a sample. so b6 will show vale of data j15 c 6 should show value of data j27 and so on

shamsu

 

[myall_blues]

=IF(OR($A$1=$A$6,$A$1=$A$10),INDIRECT("Data!$J" & 12*COLUMN()-9),0)

 

[kvsrinivasamurthy]

Avoiding Passive formula, in B6 copied across.

=IF(OR($A$1=$A$6,$A$1=$A$10),INDEX(Data!$J$1:$J$1000,15+12*(COLUMNS($B$6:B$6)-1)),0)

 

[shamsu203]

=IF(OR($A$1=$A$6,$A$1=$A$10),INDIRECT("Data!$J" & 12*COLUMN()-9),0)

=IF(OR($A$1=$A$6,$A$1=$A$10),INDIRECT("Data!$J" & 12*COLUMN()-9),0)

works fine

=IF(OR($A$1=$A$6,$A$1=$A$10),INDIRECT("Data!$J" & 12*COLUMN()-9),0)

Avoiding Passive formula, in B6 copied across.

=IF(OR($A$1=$A$6,$A$1=$A$10),INDEX(Data!$J$1:$J$1000,15+12*(COLUMNS($B$6:B$6)-1)),0)

Pls explain what passive formula means. . The earlier suggestion by myal blues works fine

 

[kvsrinivasamurthy]

INDIRECT , OFFSET are passive (volatile) formulas. Any change in worksheet will recalculate all passive formulas. If Passive formulas are in more cells it needs more time foe recalculation. Thus excel becomes slow. So, i It is always better to avoid passive formulas

 

[myall_blues]

Pls explain what passive formula means.

More commonly they are called ‘volatile’ functions.
Unless you have a very large number of formulas containing volatile functions it won’t make any practical difference.

 

[shamsu203]

Avoiding Passive formula, in B6 copied across.

=IF(OR($A$1=$A$6,$A$1=$A$10),INDEX(Data!$J$1:$J$1000,15+12*(COLUMNS($B$6:B$6)-1)),0)

if i create name ranges how will it show in the formula. in this case the range name s globlg for the j column

 

[kvsrinivasamurthy]

What is the name of range and what is the range..
If name is global and range is J15:BF15

=IF(OR($A$1=$A$6,$A$1=$A$10),INDEX(global,1+12*(COLUMNS($B$6:B$6)-1)),0)

 

[shamsu203]

if i create name ranges how will it show in the formula. in this case the range name s globlg for the j column

Range name starts from j2 for the entire column. This is the formula I used. Offset(data!$j$2,0,0,counta(data!$j:$j)-2)

 

[kvsrinivasamurthy]

What actually want now.