# Adding up cell position not values in cell

#### rayelle

##### New Member
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?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi and welcome to the board,

Try;

=INDIRECT("A"&(ROW())*143)

Thanks, Mike.
I tried using the INDIRECT function but I can't seem to get it to work for me, probably because I made a mistake in my explanation, I am actually trying to create two new columns D & E with cell values from colums A and C.
So D3=value in cell A365 (date) and E3= value in cell C365 (which contains a formula to calculate the daily average).
D1=6/16/08
E1=14.47
D2=6/17/08
E2=10.28
D3=A365=6/18/08
E3=C365=13.42

Hope that helps

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?

In Cell C1 enter the formula:
="A"&(Row()*143)
then fill this formula down as far as you require

In Cell D1 enter the formula:
="B"&(Row()*143)
then fill this formula down as far as you require

This should give you what you require

I'm unsure why you need the INDIRECT command as specified above in this scenario

Last edited:
Hi Jonathan,

Have you actually tried your suggestion? The results I get are;

B143
B286
B429
B572
B715

When the Op requires the cell values to get the value of these cells you need tio wrap it in an Indirect.

=INDIRECT("B"&(ROW()*143)) To retrieve the value of cell B143.

Mike,

I stand corrected - that's what you get for not trying it out in Excel first... doh!

Using the INDIRECT function works perfectly (don't think I've ever used that before, but I must have as I've done similar things a long while ago)

So, I figured D1 =INDIRECT("A"&(ROW()*77))
E1 =INDIRECT("C"&(ROW()*77))

D1: 6/16/08
E1: 14.34

It works beautifully, however, when I drag the cell down to fill the cells below, I get:

D2: 6/17/08, E2: (BLANK)
D3: 6/18/08, E3: (BLANK)
D4: 6/18/08, E4: (BLANK)
D5: 6/19/08, E5: (BLANK)
D6: 6/19/08, E6: (BLANK).
ETC.

Any ideas why this would be happening?

Thanks again for all your help.

Hi,

What cells are you trying to rererence in the additional formula? Is it;

A154
A231
A308
A385

Hi Mike,

Im trying to reference cells: A221 & C221, A365 & C365, A509 & C509, that is every 144 rows below.

However, when I dragged cell D1 AND E1 down, the results I get are D2 =INDIRECT("A"&(ROW()*77))
E2 =INDIRECT("C"&(ROW()*77))

Does that mean I need to change manually the 77 to 221, etc.? Is there any way I can tell excel to calculate the values automatically, since they are every 144 rows below? I thought by dragging the cell down it will automatically fill in the values.

Thank you!

Last edited:
So why did you give us this;

So, I figured D1 =INDIRECT("A"&(ROW()*77))
E1 =INDIRECT("C"&(ROW()*77))

Surely it should have been;

D1 =INDIRECT("A"&(ROW()*144))
E1 =INDIRECT("C"&(ROW()*144))

D1 would reference A144
D2 = A288
D3 = A432
etc.

ROW() return the row number, so in Row 3 the formula gives you 3*144

The formula should just copy and paste down, the row will increment on its own.

Replies
6
Views
256
Replies
4
Views
256
Replies
3
Views
1K
Replies
5
Views
311
Replies
5
Views
210

1,196,484
Messages
6,015,469
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back