Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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