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 greatfull

I need to copy some data where,

1st row is =A5
2nd row is =A25
3rd row is =A45
4th row should be =A65
and so on up to prolly 500 row

is this doable?

See More: Excel Copy Cell Question

Report •

December 2, 2009 at 05:09:08

I am not clear what your problem is.

If you have =A5 in a cell it will return the value in cell A5

Are 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 & B3
2. 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 B26
3. In cell C1 enter ="A"&B1 and drag this down to C26
4. 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 all
6. Cells C1 to C26 will now contain the formulas =A5 to =A505 in 20 row increments.

Was this what you were looking for???


Report •

December 2, 2009 at 07:41:25
I think he is trying to copy the contents of cells

upto cell A500

He's incrementing the cell number locations by 20 each time.

But he doesn't say where he wants them copied to.



Report •

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 it
A6-A24 was numbers that i SUM up on A25
A26-A44 was numbers that i SUM on A45
and so on

and i need to get the sum of these numbers to be copied on, for example C1-C26.

so on C1 cell =A5
C2 cell =A25
C3 cell =A45 and so on.

i tried to series these numbers.. but i cant
please help, thanks in advance

Report •

Related Solutions

December 2, 2009 at 10:31:49

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.


Report •

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 •

December 2, 2009 at 23:03:10
because lets say
on c1 cell i put =A5
on C2 i put =A25
on C3 i put =A45
and when i drag C1:C3 and i series that,
on C4 =A8
on C5 =A28
on C6 =A48
on C7 =A11
on C8 =A31
on C9 =51
and so on

but basiclly i want the result to be :
C1 =A5
C2 =A25
C3 =A45
C4 =A65
C5 =A85
C6 =A105
and so on.

Is this doable in Excel? please help with this matter and i really thankful in advance for the helps

Report •

December 3, 2009 at 05:19:49

The solution I offered in response #1 will do this for you, giving you
=A5 in C1
and =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 5
In cell C2 enter =INDIRECT("A" & D2)
In cell D2 enter 25

Now select all four cells C1 to D2
Drag them down the required number of rows.


Report •

December 5, 2009 at 06:04:56
dear humar,
it works.. thanks for the help =)

but on the second thought, is there anyway we can simplify this formula, rather than having 2rows for 1 formula?

Report •

December 7, 2009 at 14:10:07

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:

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.


Report •

Ask Question