Help

[J.C.]

Is there a way to increment the Row Number in a formula or a better way to sort my data.

On sheet 1 I have data for multiple items that is imported from a program and it is all over the place

On sheet 2 I am trying to organize the data in to rows per item.

So in R1CA the formula =Sheet1!D10
Then in R2CA the formula =Sheet1!D40
R3CA =Sheet1!D70
.......

I cant copy the formula and excel understand it as a series. I would think that there should be a way to increment the Row number in the formula. I have hundreds of these to do. Any help would be appreciated.

 

[Biff]

Hi JC,

Try this:

I assume R1CA is A1, R2CA is A2.....

A1 formula =Sheet1!D10
A2 formula =OFFSET(Sheet1!$D$10,(ROW()-1)*30,)

Copy down as needed.

Biff

-----Original Message-----
Is there a way to increment the Row Number in a formula

or a better way to sort my data.

On sheet 1 I have data for multiple items that is

imported from a program and it is all over the place

On sheet 2 I am trying to organize the data in to rows per item.

So in R1CA the formula =Sheet1!D10
Then in R2CA the formula =Sheet1!D40
R3CA =Sheet1!D70
......

I cant copy the formula and excel understand it as a

series. I would think that there should be a way to
increment the Row number in the formula. I have hundreds
of these to do. Any help would be appreciated.

 

[Tushar Mehta]

In R1CA, enter =INDIRECT("Sheet1!D"&(ROW()-1)*30+10). Copy as far down
column A as desired.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions