Drag Formula Question...

Hi once again

I actually have been facing this issue for quite some time and hav'nt taken the time
to get my question out there on this. So here it is, Hope there's a solution...

Is it possible, or how do you, drag a particular formula and have the increments
focused on ONE data you want accumulated (Increased)? I know this sounds
confusing, but it is hard to explain, so here an example of what I'm asking:

I have the need to drag down a formula 1000 rows, but I only want the
"Sheet#" (worksheets) incremented as I drag, but I want the cell "B2" to remain
constant... The formula looks like this: =Sheet1!B2 ... I want to drag this
formula down so it resembles this:

=Sheet1!B2
=Sheet2!B2
=Sheet3!B2

on and on....

When I drag I get increments in the cell B2 only:

=Sheet1:B2
=Sheet1:B3
=Sheet1:B4

etc...

is there a way to tell the drag to increase the sheets only without incrementing
the cell B2?

This has boggled me for sometime now, I hope it's possible

Thank You For Any Help On This...

Originally Posted by Mhz

Hi once again

I actually have been facing this issue for quite some time and hav'nt taken the time
to get my question out there on this. So here it is, Hope there's a solution...

Is it possible, or how do you, drag a particular formula and have the increments
focused on ONE data you want accumulated (Increased)? I know this sounds
confusing, but it is hard to explain, so here an example of what I'm asking:

I have the need to drag down a formula 1000 rows, but I only want the
"Sheet#" (worksheets) incremented as I drag, but I want the cell "B2" to remain
constant... The formula looks like this: =Sheet1!B2 ... I want to drag this
formula down so it resembles this:

=Sheet1!B2
=Sheet2!B2
=Sheet3!B2

on and on....

When I drag I get increments in the cell B2 only:

=Sheet1:B2
=Sheet1:B3
=Sheet1:B4

etc...

is there a way to tell the drag to increase the sheets only without incrementing
the cell B2?

This has boggled me for sometime now, I hope it's possible

Thank You For Any Help On This...

hI,

One way,

=Indirect("Sheet"&Row()-3&"!B2")

adjust the Row()-3 to suit.

hth
---

Originally Posted by EdMac

Bryan,

You always go where I forget to go! You are a master of the row() and Column() functions

Ed

they can be useful sometimes.
---

Samuel, your info will be very useful in the future for me, although the "Sheets"
does not increment when dragging, but this is Very Useful for future references... Thanks Alot!

Bryan, That is definately the formula I needed! Although, I didn't understand
what the World that (-3) was all about until I tampered around with it..

I see it actually subtracts the row count you are using the formula on.. How
Unique..! I used to do BASIC programming back in the early 80s, but some
of the new formulas with VB is a bit menacing to me now.. Dropped
out of programming in the mid 80s.. I Really Appreciate all The forum's Help..
I would be a 'Nut Case' Without the wonderful help Here... THANKS TO YOU ALL....

Works great now, and added knowledge for this challeng in the future...

Originally Posted by Mhz

Samuel, your info will be very useful in the future for me, although the "Sheets"
does not increment when dragging, but this is Very Useful for future references... Thanks Alot!

Bryan, That is definately the formula I needed! Although, I didn't understand
what the World that (-3) was all about until I tampered around with it..

I see it actually subtracts the row count you are using the formula on.. How
Unique..! I used to do BASIC programming back in the early 80s, but some
of the new formulas with VB is a bit menacing to me now.. Dropped
out of programming in the mid 80s.. I Really Appreciate all The forum's Help..
I would be a 'Nut Case' Without the wonderful help Here... THANKS TO YOU ALL....

Works great now, and added knowledge for this challeng in the future...

good to see it working for you, and yes, using the Row() or Column() to increment or decrement a number is often useful. Thanks for the feedback.
---