# Excel Copy Cell Question

Microsoft Excel 2003 (full product)
December 1, 2009 at 23:46:55
Specs: Windows Vista
 hi guys, i have this problem while doing excel. im not sure if this is do able, but if anyone got the solutions, i would be greatfullI need to copy some data where,1st row is =A52nd row is =A253rd row is =A454th row should be =A65and so on up to prolly 500 rowis this doable?

See More: Excel Copy Cell Question

#1
December 2, 2009 at 05:09:08
 Hi,I am not clear what your problem is.If you have =A5 in a cell it will return the value in cell A5Are cells A5, A25 etc. on the same worksheet or on a different worksheet, and which cells contain your formulas =A5 etc.If your problem is how to create a series of formulas =A5, =A25, =A45 up to =A505, without entering each one individually then do this:1. Enter 5, 25 and 45 in the first three cells, say B1, B2 & B32. Select the three cells and drag them down, to extend the series. This will give you 5 to 505 in increments of 20, with 505 in cell B263. In cell C1 enter ="A"&B1 and drag this down to C264. Select all the cells C1 to C26, Copy then paste special Values.5. With C1 to C26 still selected Use Find and Replace with "A" in Find and "=A" in replace. Replace all6. Cells C1 to C26 will now contain the formulas =A5 to =A505 in 20 row increments.Was this what you were looking for???Regards

Report •

#2
December 2, 2009 at 07:41:25
 I think he is trying to copy the contents of cellsA5A25A45A65upto cell A500He's incrementing the cell number locations by 20 each time.But he doesn't say where he wants them copied to.MIKEhttp://www.skeptic.com/

Report •

#3
December 2, 2009 at 08:56:19
 i apology for unclear problem and yeah, i was trying to copy contents of cells as mike said.on A5 cell got certain numbers in itA6-A24 was numbers that i SUM up on A25A26-A44 was numbers that i SUM on A45and so onand i need to get the sum of these numbers to be copied on, for example C1-C26.so on C1 cell =A5C2 cell =A25C3 cell =A45 and so on.i tried to series these numbers.. but i cant please help, thanks in advance

Report •

Related Solutions

#4
December 2, 2009 at 10:31:49
 Hi,The solution I suggested allows you to create the formulas to 'copy' the data in cells A5, A25, A45 etc. without having to enter each formula individually.An alternative approach is to use a user defined function that will sum data in blocks of any size.If your data did not have any subtotals in it, and it was continuous data, there is a formula that will sum consecutive blocks of 19 cells (or any other value, e.g., 3 for three months data into quarterly data) and display the subtotals either in a horizontal or a vertical table. Once you have the user defined function, you only need enter the formula once, then drag it the required number of cells.The user defined function and instructions are here.Regards

Report •

#5
December 2, 2009 at 21:58:29
 thanks for the reply =) But i was looking if there's a possibility of serializing of data with 20 increment of rows. thanks for the help

Report •

#6
December 2, 2009 at 23:03:10
 because lets say on c1 cell i put =A5on C2 i put =A25on C3 i put =A45and when i drag C1:C3 and i series that,on C4 =A8on C5 =A28on C6 =A48on C7 =A11on C8 =A31on C9 =51and so onbut basiclly i want the result to be :C1 =A5C2 =A25C3 =A45C4 =A65C5 =A85C6 =A105and so on.Is this doable in Excel? please help with this matter and i really thankful in advance for the helps

Report •

#7
December 3, 2009 at 05:19:49
 Hi,The solution I offered in response #1 will do this for you, giving you =A5 in C1and =A25 in C2 and A45 in C3 and so on.It's not a single step process, but it works.An alternative is to use the INDIRECT() function. This is one of Excel's volatile functions which recalculates with every change, not just changes in the cells referenced by the formula. If you have a lot of formulas using INDIRECT it will slow down Excel.In cell C1 enter =INDIRECT("A" & D1)In cell D1 enter 5In cell C2 enter =INDIRECT("A" & D2)In cell D2 enter 25Now select all four cells C1 to D2Drag them down the required number of rows.Regards

Report •

#8
December 5, 2009 at 06:04:56
 dear humar,it works.. thanks for the help =)cheersbut on the second thought, is there anyway we can simplify this formula, rather than having 2rows for 1 formula?

Report •

#9
December 7, 2009 at 14:10:07
 Hi,Did you mean two columns for one formula?Here is an alternative that works in one column.Assuming cells A5 to A505 contain the values you want to copy.In cell B5 enter this formula:`=OFFSET(A\$1,4+(CELL("row",A5)-CELL("row",A\$5))*20,0)`Note the \$ signs.Drag the formula down from B5 to B30 and you will get the values in cells A5, A25, A45 etc. down to A505.Regards

Report •