Home |
Search |
Today's Posts |
#1
|
|||
|
|||
copy to different worksheet
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 |
#2
|
|||
|
|||
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! ================================================== === "jskamm" wrote in message ... 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 |
#3
|
|||
|
|||
Thanks, it worked great!
Jskamm "RagDyeR" wrote: 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! ================================================== === "jskamm" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote: 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! ================================================== === "jskamm" wrote in message ... 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 |
#5
|
|||
|
|||
Try this:
=INDEX(Sheet1!$A$4:$E$4,ROW(A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jskamm" wrote in message ... 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" wrote: 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! ================================================== === "jskamm" wrote in message ... 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 |
#6
|
|||
|
|||
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" wrote: Try this: =INDEX(Sheet1!$A$4:$E$4,ROW(A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jskamm" wrote in message ... 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" wrote: 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! ================================================== === "jskamm" wrote in message ... 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 |
#7
|
|||
|
|||
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! ============================================== "jskamm" wrote in message ... 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" wrote: Try this: =INDEX(Sheet1!$A$4:$E$4,ROW(A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jskamm" wrote in message ... 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" wrote: 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! ================================================== === "jskamm" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to copy and edit then delete a worksheet | Excel Discussion (Misc queries) | |||
INDIRECT Function impact on Copy Worksheet | Excel Worksheet Functions | |||
Copy cells to another worksheet | Excel Discussion (Misc queries) | |||
copy a cell to another worksheet? | Excel Worksheet Functions | |||
Copy worksheet with Pivot Table and break link to original workshe | Excel Worksheet Functions |