I did run into one problem though... On a new sheet I need to continue the formula to leave off from where I left on Sheet 2. For Example on Sheet 3 I need:
Sheet 1, Cell A22 = Sheet 3, Cell A2
Sheet 1, Cell A23 = Sheet 3, Cell A7
Sheet 1, Cell A24 = Sheet 3, Cell A12
Currently, when I input the formula, it starts at A2 again.
Let's break down how the formula I wrote for you works.
First, let's start off with the outside.
Code:
[COLOR=#ff0000]=IF(MOD(ROW(),5)=2[/COLOR],INDIRECT("Sheet1!B" & (ROW()+8)/5),[COLOR=#ff0000]""[/COLOR])
You wanted the formula to appear in every 5th row, starting at row 2.
The key here is the MOD and ROW() functions.
If you use the ROW() function with no arguments, it simply returns the row number the formula is located in (try putting
=ROW() in any cell and copy down).
And the MOD function simply returns the remainder when dividing two numbers (see:
https://www.techonthenet.com/excel/formulas/mod.php)
So, we want the formula to appear in any row number that when we divide the row by 5, the remainder is 2. So, that ID formula checks to see if the remainder is two. If it is, it evaluates the inside part. If it is not, it returns nothing ("").
That takes care of the blank rows.
Now, whenever we want to dynamically build a range reference in Excel, we have to use the INDIRECT function so that Excel knows we are talking about a range reference, and not a literal text string. So if we want to return the value from Sheet1, cell B2, if we wrote that reference dynamically, it would look like:
=Sheet1!B2
However, we want to build a formula to return this based on the row number that the formula is in. So we need to come up with that formula. We know that the "Sheet1!B" part won't change, just the row number. That is where this part comes in:
(ROW()+8)/5
Because for every 5 rows we go down, we want our row number to increase by 1, we divide by 5.
(If we had the situation where for every one row we went down, we want our row number reference to increase by 5, we would multiple by 5.
So we are always going to multiply/divide by the number of rows we are "jumping".
The last part, the +8, is just a number that you plug in to get it to work, and is dependent on which row you are starting on. So you can back into that number.
So, when we plug in a 2 for ROW(), we want to return 2.
When we plug in a 3 for ROW(), we want to return 3.
When we plug in a 4 for ROW(), we want to return 4.
etc.
So let's try the first one:
(ROW()+?)/5=2
(2+?)/5=2
2+?=10
?=8
If we plug the 8 into the next one, we get:
(7+8)/5
15/5
3
And the next:
(12+8)/5
20/5
4
So we can see that it works!
So, the way I usually approach this, is go to the cell that I want return my value in via formula, and work on first just getting the row part right:
=(ROW()+8)/5
If I have it working right, when I copy it down rows, it should return the row reference number I need.
Then, we just build around that.
Hope that makes sense!
