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 rowis this doable?

Hi, 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???

Regards

I thinkhe is trying to copy the contents of cells

A5

A25

A45

A65upto cell A500

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

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

MIKE

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 onand 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

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

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

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 onbut 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

Hi, 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 25Now select all four cells C1 to D2

Drag them down the required number of rows.Regards

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?

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

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History