Adding up cell position not values in cell

[rayelle]

I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.
The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.
How can I tell excel to grab these values located every 144 rows below and create two new columns?
Thanks for your help.

 

[rayelle]

Because:

D1 & E1 should reference A77 & C77, correspondingly.
D2 & E2 should reference A221 & C221.
D3 & E3 should reference A365 & C365.
D4 & E4 should reference A509 & C509.
D5 & E5 should reference A653 & C653.
And so on...

The results should look like this:
D1: 6/16/08, E1: 14.34
D2: 7/17/08, E2: 10.97

I think the problem is that 221/2 = 110.5, 365/3 = 121.66666, etc. so the numbers are not even numbers.

Any suggestions on how to work around that?

 

[Mike Blackman]

Hi,

In that case try this;

=IF(ROW()=1,INDIRECT("A"&(ROW()*77)),INDIRECT("A"&(((ROW()-1)*144)+77)))

 

[rayelle]

Thank you, Mike.

That worked perfectly!

Thanks a lot for all your help.