How To Increase Cell Reference By More Than 1

0

I have a lot of data in a column. I want to use the data from cells on every 7th row, so I want to reference cells in every 7th row.

My first reference is  "=P8"

If I copy that cell and paste it below, I get "=P9", but I want it to be "=P15".

I have tried "=P8+7" and "=P(8+7)" and variations but they give errors such as #VALUE! or #NAME!

I think the solution may be simple, but my mind is a blank!

Thanks for any help you can give.

 - Baht

Answer
Discuss

Discussion

Thanks very much for the advice - some very interesting functions in there.

I decided to make it a little less complicated by adding an extra column (D) which contains the number 1 in the first cell, which is then incremented by 7 on each row. So this removes the requirement for the "ROWS" part of your expression.

The data I want is now in column Q, cells 1,8,15, 22, etc.

So to get the data, the cells on subsequent rows contain:

=INDIRECT("Q"&D7)
=INDIRECT("Q"&D8)
=INDIRECT("Q"&D9)
=INDIRECT("Q"&D10)
etc

Thanks again.
Baht_Simpson (rep: 2) Aug 14, '16 at 5:18 pm
if you want to  take the cells 1,8,15,22 etc.
then replace in the formula the number 8 by the number 1
so:
=INDIRECT("P"&7*(ROWS($A$1:A1)-1)+1)
salim_hasan (rep: 40) Aug 16, '16 at 5:39 pm
Add to Discussion

Answers

0
Selected Answer

tray this formula

=INDIRECT("P"&7*(ROWS($A$1:A1)-1)+8)

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login