copy to different worksheet

[jskamm]

Here's my problem.

In worksheet 1, I have summation formulas in a one row, at a4, b4, c4, d4
and e4.
I want to put the results of the above sum formulas into a different
worksheet and put them in one column, such as a1, a2, a3, a4 and a5.

Can anyone help me?'

Thanks

 

[RagDyeR]

Try this in A1 of Sheet2, and copy down:

=INDEX(Sheet1!$A$4:$E$4,ROW())
--

HTH,

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


Here's my problem.

In worksheet 1, I have summation formulas in a one row, at a4, b4, c4, d4
and e4.
I want to put the results of the above sum formulas into a different
worksheet and put them in one column, such as a1, a2, a3, a4 and a5.

Can anyone help me?'

Thanks

 

[jskamm]

Thanks, it worked great!
Jskamm

 

[jskamm]

This works if I put the index formula at row 1, column 1,"A1". can you tell
me how to change the index formula if I want to insert than copy down at say
row 10 in column b, "B10".

 

[RagDyer]

Try this:

=INDEX(Sheet1!$A$4:$E$4,ROW(A1))

--
HTH,

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

 

[jskamm]

RagDyer,

Thanks so much, Again it works great.
Can you explain to a layman why the "A1" worked verses the row().
This really helped me out. Keep up the good work

jskamm

 

[RagDyer]

Enter this formula in say B10:

=INDEX(Sheet1!$A$4:$E$4,1)

And copy down to B14.

That will return the same value from A4 into all the cells,
because the formula is referencing the first cell of the indexed range,
A4:E4

Now, if you change each of the formulas by incrementing that last number to
2, 3, 4, and 5, you'll get the value in each of the cells in the indexed
range.

BUT ... who wants to manually revise formulas in this way?

As you saw when you copied down, the number 1 doesn't automatically
increment itself, as a cell address might.
And this form of the Index formula wants a number, not an address.

Now enter
=ROW()
in any cell.
You'll get a number, which represents the row the formula is in.
Copy it down and you'll see that although the formula remains unchanged, the
number increments due to the change in row (placement) number.

This was the original formula I suggested to you, where the formula started
in Row1.
The value of the number starts at the starting row number, and increments
automatically, making it unnecessary to manually change the numbers in the
Index formula.

Since you wanted to start your Index formula in Row10, you could very well
have used this formula:

=INDEX(Sheet1!$A$4:$E$4,ROW()-9)

Which I'm sure you understand by now, would equate to,
10-9 = 1 (your starting position in the Indexed range).

This formula however, would require a change any time you chose to use it in
a different location (starting row).

So I chose another alternative.

Enter this in B10:
=ROW(A1)

This gives you a 1, no matter which row you enter it into.
And of course, copying it down the column, increments itself the same as
ROW() does, BUT, not for the same reason.
You'll see that the formula changes as it's copied down, to ROW(2), ROW(3),
.... etc., which returns a number that is completely independent of it's
actual location (row placement).

--
HTH,

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