Dragging formula
 

Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number needs
to be dragged in such a way that for each cell dragged down (from B100 to
B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so
on...)

Thanks in advance

Dragging formula
 

On Nov 14, 3:20 pm, Sasikiran
wrote:
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number needs
to be dragged in such a way that for each cell dragged down (from B100 to
B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so
on...)

Thanks in advance


=C$7

should work fine

hth Carlo

Dragging formula
 

Hey Carlo,

Looks like my query was not clear...

I have a formula =C7 in B100 while dragging it vertically its automatiically
taking the next reference cell of the same column C (c7, c8, c9)

Actullay I need the formula in this order C7, F7, I7, L7... so on

Tried with =C$7 also but its not working...

Kindly help..

"carlo" wrote:

On Nov 14, 3:20 pm, Sasikiran
wrote:
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number needs
to be dragged in such a way that for each cell dragged down (from B100 to
B110 and so on) every third column should be referred (i.e C7, F7, I7, L7 so
on...)

Thanks in advance


=C$7

should work fine

hth Carlo

Dragging formula
 

=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
--
David Biddulph

"Sasikiran" wrote in message
...
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number needs
to be dragged in such a way that for each cell dragged down (from B100 to
B110 and so on) every third column should be referred (i.e C7, F7, I7, L7
so
on...)

Thanks in advance

Dragging formula
 

Hello David...

Thanks a lot.. another query regarding the same..

Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100)))
horizontally to the next cell which should refer to C12 (thats *5 rows each
time its dragged horizantally) and the row reference ROW(B100) in the above
formula becomes C100 (that means the row reference will be *1 each time its
dragged)

Can a formula be put in B100 which can be dragged horizantally and
vertically with the above specifications..

Thanks in advance...


"David Biddulph" wrote:

=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
--
David Biddulph

"Sasikiran" wrote in message
...
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number needs
to be dragged in such a way that for each cell dragged down (from B100 to
B110 and so on) every third column should be referred (i.e C7, F7, I7, L7
so
on...)

Thanks in advance

Dragging formula
 

If you look at the syntax of the OFFSET and ROW functions (see Excel help),
you'll see how my formula works, so you'll be able to adjust it to your new
requirements.
--
David Biddulph

"Sasikiran" wrote in message
...
Hello David...

Thanks a lot.. another query regarding the same..

Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100)))
horizontally to the next cell which should refer to C12 (thats *5 rows
each
time its dragged horizantally) and the row reference ROW(B100) in the
above
formula becomes C100 (that means the row reference will be *1 each time
its
dragged)

Can a formula be put in B100 which can be dragged horizantally and
vertically with the above specifications..

Thanks in advance...


"David Biddulph" wrote:

=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
--
David Biddulph

"Sasikiran" wrote in message
...
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number
needs
to be dragged in such a way that for each cell dragged down (from B100
to
B110 and so on) every third column should be referred (i.e C7, F7, I7,
L7
so
on...)

Thanks in advance

Dragging formula
 

Try this formula *anywhere*, and drag across and down,
And see if it meets your specs:

=INDEX($C$7:$IV$1000,5*COLUMNS($A:A)-4,3*ROWS($1:1)-2)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Sasikiran" wrote in message
...
Hello David...

Thanks a lot.. another query regarding the same..

Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100)))
horizontally to the next cell which should refer to C12 (thats *5 rows each
time its dragged horizantally) and the row reference ROW(B100) in the above
formula becomes C100 (that means the row reference will be *1 each time its
dragged)

Can a formula be put in B100 which can be dragged horizantally and
vertically with the above specifications..

Thanks in advance...


"David Biddulph" wrote:

=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
--
David Biddulph

"Sasikiran" wrote in message
...
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number
needs
to be dragged in such a way that for each cell dragged down (from B100
to
B110 and so on) every third column should be referred (i.e C7, F7, I7,
L7
so
on...)

Thanks in advance

Dragging formula
 

Fantastic Rag...

Thats really great.. Its working good..
If you can take some time of yours to understand me the formula that would
be greatful..

Thanks and anticipating your reply


"RagDyeR" wrote:

Try this formula *anywhere*, and drag across and down,
And see if it meets your specs:

=INDEX($C$7:$IV$1000,5*COLUMNS($A:A)-4,3*ROWS($1:1)-2)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Sasikiran" wrote in message
...
Hello David...

Thanks a lot.. another query regarding the same..

Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100)))
horizontally to the next cell which should refer to C12 (thats *5 rows each
time its dragged horizantally) and the row reference ROW(B100) in the above
formula becomes C100 (that means the row reference will be *1 each time its
dragged)

Can a formula be put in B100 which can be dragged horizantally and
vertically with the above specifications..

Thanks in advance...


"David Biddulph" wrote:

=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
--
David Biddulph

"Sasikiran" wrote in message
...
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number
needs
to be dragged in such a way that for each cell dragged down (from B100
to
B110 and so on) every third column should be referred (i.e C7, F7, I7,
L7
so
on...)

Thanks in advance

Dragging formula
 

You're welcome, and thank you for the feed-back.

As to explaining the workings of the formula:

Index() is a function that has 2 forms.
It can either return a value or it can return a reference.

In this case we're using it to return values.

Put simply, you can index a range, and then refer to a cell in that range.
Your starting point is C7, and I arbitrarily chose the last Column (IV) as
the ending column at Row1000.

The first argument in Index() is the range, the 2nd is the row, and the 3rd
is the Column.

So, =Index(C7:IV1000,1,1) would return the contents of C7, since
*everything* starts at the *start* of the indicated range.

Index(C7:IV1000,2,1) would return the contents of C8, while
Index(C7:IV1000,1,2) would return the contents of D7.

What we therefore need to do, is figure a way to increment the 2nd and 3rd
arguments, so that the rows and columns will change in the appropriate
series that we need, as we copy the formula.

The 2 best functions for this are Columns() and Rows().

In any cell, try:
=Columns(A:A)

Copy across a few columns and down a few rows.
You'll see that the return is "1", and it doesn't change with the copying.

Change it to:
=Columns($A:A)
And do the same, and you'll see that it increments when copying across, but
*does not* increment as you copy down.
This is because you anchored the function at "A" ( 1 ), while allowing the
second column reference to change.

Do the same test with the Rows() function,
=Rows(1:1)
and you'll see that the same thing happens, except it'll increment when
copied down and not when copied across.

So you can see how this would be useful in a formula that you want to copy
in 2 directions, with different references changing dependent on the
direction of the copy.

Therefore, you should now understand how:
=Index(C7:IV1000,Columns($A:A),Rows($1:1))
Would return the contents of C7.

Don't be confused ... as stated above ... the 2nd Index() argument
determines *ROWS*,
We're using the Columns() function in the 2nd argument because we want the
ROWS to increment as we copy *across*,
And the 3rd argument (Columns), to increment as we copy down.

The rest of the formula is simply a mathematical calculation to enable the
row and column references to follow the numerical series we need as they are
copied down and across.

Take:
=5*Columns($A:A)-4
And copy across and down, and take note of the returns.

Do the same with:
=3*ROWS($1:1)-2

After you understand the workings of Index(), the difficulty in completing
the formula is simply figuring out the mathematics to attain the numerical
sequence you're looking for.
--
Regards,

RD
----------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------------

"Sasikiran" wrote in message
...
Fantastic Rag...

Thats really great.. Its working good..
If you can take some time of yours to understand me the formula that would
be greatful..

Thanks and anticipating your reply


"RagDyeR" wrote:

Try this formula *anywhere*, and drag across and down,
And see if it meets your specs:

=INDEX($C$7:$IV$1000,5*COLUMNS($A:A)-4,3*ROWS($1:1)-2)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Sasikiran" wrote in message
...
Hello David...

Thanks a lot.. another query regarding the same..

Now I need to drag the formula =OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100)))
horizontally to the next cell which should refer to C12 (thats *5 rows
each
time its dragged horizantally) and the row reference ROW(B100) in the
above
formula becomes C100 (that means the row reference will be *1 each time
its
dragged)

Can a formula be put in B100 which can be dragged horizantally and
vertically with the above specifications..

Thanks in advance...


"David Biddulph" wrote:

=OFFSET(C$7,0,3*(ROW(B100)-ROW(B$100))) and copy down
--
David Biddulph

"Sasikiran" wrote in message
...
Hi,

I have a problem while dragging the formula.

Its better to give an example rather than explaining in the normal
way.

I have a formula =C7 in the cell B100 and =F7 in B101..

That means the row number remains the same (7) but the column number
needs
to be dragged in such a way that for each cell dragged down (from
B100
to
B110 and so on) every third column should be referred (i.e C7, F7,
I7,
L7
so
on...)

Thanks in advance