Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
Click to expand...
or a better way to sort my data.
On sheet 1 I have data for multiple items that is
Click to expand...
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
Click to expand...
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.
 
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
 
Back
Top