can you drag this down to save time????

[cjohnsen]

If i have a formula that is referencing another sheet such as
='sheet 1'!T3300

and the next row needs to be

='sheet 1'!T3325

adding 25 each new row.... is there a way i can drag this down as opposed to changing every formula individually by adding 25?


thanks in advance for all your help!

[Maistrye]

If i have a formula that is referencing another sheet such as
='sheet 1'!T3300

and the next row needs to be

='sheet 1'!T3325

adding 25 each new row.... is there a way i can drag this down as opposed to changing every formula individually by adding 25?


thanks in advance for all your help!

Try (assuming A1 is the starting cell):

=INDIRECT(ADDRESS(3275+ROW(A1)*25,COLUMN(T:T),1,1,"sheet 1"))

3275 + ROW(A1)*25 could also be 3300 + (ROW(A1)-ROW($A$1))*25, if it makes more sense. Actually, the second is more robust.

Scott

[cjohnsen]

how can i change it when my sheet goes to 30 lines instead of 25 and again to 40 lines???


Thanks!

[Maistrye]

how can i change it when my sheet goes to 30 lines instead of 25 and again to 40 lines???


Thanks!

Does the number of lines between items remain constant?

If it does, instead of putting 25 in the formula, you can put a reference to a cell, say B1 instead of 25. Then you put:

=INDIRECT(ADDRESS(3300 + (ROW(A1)-ROW($A$1))*$B$1,COLUMN(T:T),1,1, "sheet 1"))

This way, you can change the number of lines just by changing 25 to 30, etc. If there is a way to calculate the the number you want it to be, you can put that in there too.

If the distance between lines varies, ie. you want T3300, T3325, T3370, T3390, you'll have to do something different. Someone else may have to help you with that, but one possibility is to start at a specific cell (T3300). Then, for each successive line, you use an OFFSET to choose the range subsequent to the previous value you found and look in that range to find the next row you want to use. I'd like to go into more detail, but I'd have to test it, and I don't have time for that now. :-P

Someone else may be able to provide you with more details, or another alternative.

Sorry about the vagueness.
Scott

[cjohnsen]

the number of lines chang about every 10,000 lines. going from 25 rows in between for 10,000 lines then the next data set goes to 30 for 10,000 rows then 40. i may have to go 50 later on

the cell i need sums the 25 rows in between. so to drag the previous formula down, works great and saves me Tons of time! only when the rows in between the value needed goes to 30 then 40 that formula no longer works.

let me know if i can provide any more info....

[Maistrye]

the number of lines chang about every 10,000 lines. going from 25 rows in between for 10,000 lines then the next data set goes to 30 for 10,000 rows then 40. i may have to go 50 later on

the cell i need sums the 25 rows in between. so to drag the previous formula down, works great and saves me Tons of time! only when the rows in between the value needed goes to 30 then 40 that formula no longer works.

let me know if i can provide any more info....

The simplest way would probably be to have an extra column to indicate which row you going to grab data from. I'll just say it's Column C so that it is consistent with previous lines.

In A1 and down:
=INDIRECT(ADDRESS(C1,COLUMN(T:T),1,1, "sheet 1"))

T3300 is the first row, T13300 is where the increment changes, T23300 is where it changes again, etc.

In C1:
= ROW('sheet 1'!T3300)

In C2 and down:
= IF(C1<ROW('sheet 1'!T13300),C1+25, IF(C1<ROW('sheet 1'!T23300),C1+30,C1+40))

To combine these two formula is possible, but it will be more complicated.

Scott