Solved How to increment only by 1, when copy more than 1 row?

Microsoft corporation Office 2010 profes...
December 19, 2014 at 03:07:15
Specs: Windows 7
Hello all

I have some values in sheet1, column 1. In sheet2 I have some spread out fields where I need those references to those values. I have B15, B25, B100 and in those fields I would like to have the values from A1, A2, and A3. When I put in a reference in B15: =Sheet1!A1 and then copy this formula to B25 it changes the formula to =Sheet1!A11. It increments by the number of rows below the row I am copying from.

I could make a simple =Sheet1!A1 and so on in all of those fields, but there are like 100's of those fields. Any chance that I can increment the reference by one, no matter how many cells below the cells I copy the formula from?

See More: How to increment only by 1, when copy more than 1 row?

Report •

December 19, 2014 at 07:28:24
I don't know if this will work for you because it is based exactly on the cell references that you used in your example.

If you enter this formula in any cell in Rows 15, 25, or 100, it will return the values from Sheet1!A1, A2 or A3 respectively.


How it works is that the formula determines which Row it resides in and then references the proper cell based on that.

As written it is locked into those three Rows and only references Sheet1!A1, A2 and and A3, based on your example. You said "there are like 100's of those fields" so I can't offer anything else unless I know some specifics behind that statement.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

December 19, 2014 at 09:55:54
I see, that could be a solution, but is still a very locked code.

What if the increment always is the same, like if there is 30 rows between each cell that should recieve the next number, A1, A2, A3, A4 and so on..?

Ive tried all different approaches to this today, like using some row calculations too, but I always end up with the problem that it doesnt increment from A1 to A2 and from A2 to A3 and so on when copied.

Report •

December 19, 2014 at 11:19:06
✔ Best Answer
If the incremented number of rows will always be the same, then I think the following solution will work. In fact, you will have some degree of latitude as to which rows you use as long as you can fit your desired rows within that incremented range. I know that doesn't make sense right now, but read on and it should become clearer.

I'm going to start in B5 and use an increment of 30. I will copy my formula into B35, B65, and B95, returning the values in Sheet1 A1, A2, A3 and A4 respectively.
Obviously you may have to modify this to meet your exact needs.

The first thing we need is a Helper Column. A Helper Column is a column that is used to return an intermediate value that we will use in subsequent formulas. In my example, I will use Sheet2 Column A as my Helper Column.

In Sheet2!A5, enter a 1

In Sheet2!A6, enter this formula and drag it down as far as you need to:


This should result in a series of 1's in A5:A34, then 2's in A35:64, then 3's, etc. Basically the values in Column A will increment by 1 every 30 rows.

Now, in B5, enter this formula:


The INDIRECT function uses a text string as a cell reference, so B5 should return the value in SHEET1!A1 since there is a 1 in SHEET2!A5.

If you then copy that formula and paste it in B35, you will get:


Since SHEET2!A35 = 2, this formula will reference SHEET1!A2

In essence, whatever row you copy that formula into will return the value from Sheet1, Column A(whatever value is in that same row on Sheet2)

So, when I said you have some latitude as far as which rows you choose I meant that with an increment of e.g. 30, you can return the value from e.g. SHEET1!A2 in any cell from SHEET2!B35:B64.

It might be tough to find an increment that will work with B15, B25, B100 since B100 is so far from B25, but maybe you can find a formula for the Helper Column that produces a 1 in A15, a 2 in A25 and a 3 in A100. The concept of using INDIRECT would be the same, your helper formula would just be different than in my example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

December 19, 2014 at 11:49:54
Okay, that is a good solution. That works just better as I could have hoped for. Thank you very much, that saved me a lot of time. I was beginning to get worried if I had to alter all those formulas manually.

Btw. I am just adjusting the location of my formulas to the helper column, so no need for me to make a fancy algorithm to get the numbers in the helper column right.

Report •

Ask Question