Reference same cell every nth row?

[rlexcel101]

Hey all new here great forum..

Need some help with formula.

I am referencing same value from another tab in same workbook and I want to drag the formula down but only include the fixed value in every 4th row. I tried OFFSET and ROW but it seems to not be working.

 

[gmhumphr]

I would recommend using Formulas > Evaluate Formula to audit the function you put together, because you have the right concept on how to achieve your result (row + offset). Try this framework:


=OFFSET($[first record in source sheet],(ROW([first row in destination file where you want result])-[first row in destination file where you want result])*4,0)

The source should be hard referenced (absolute - frozen with $)
The ROW function should increase as you copy down (relative reference - no $)
Hard code a number for the first row, or use ROW again and this time hard reference it

 

[rlexcel101]

I would recommend using Formulas > Evaluate Formula to audit the function you put together, because you have the right concept on how to achieve your result (row + offset). Try this framework:


=OFFSET($[first record in source sheet],(ROW([first row in destination file where you want result])-[first row in destination file where you want result])*4,0)

The source should be hard referenced (absolute - frozen with $)
The ROW function should increase as you copy down (relative reference - no $)
Hard code a number for the first row, or use ROW again and this time hard reference it

Hmmm still not working



=OFFSET(source value), ROW(confused on what I put here???),*4,0

 

[gmhumphr]

Hmmm still not working



=OFFSET(source value), ROW(confused on what I put here???),*4,0

Close quotes from offset too early.

OFFSET takes the following
>Starting point (a single cell reference from which you will move. This should be the first item you want to return from the source sheet.) Example: Source!$A$5
>Rows (how many rows you want to move from the original referenced cell. This should be a rolling increment of 4, which will involve some trickery using ROW which I will explain below) Example: 4 (but better if dynamic and increasing, see ROW description)
>Cols (how many rows you want to move from the original referenced cell. You only want to move down, so said your data is vertical, so this should be 0) Example: 0

If you stopped with these inputs as I described, your formula would return the value in cell Source!$A$9 (A5, offset down 4 rows (5+4=9), and offset 0 columns right (A+0=A).

This achieves your desire of offsetting by 4, but doesn't allow you to copy the formula down and keep looking every 4th item. How do we achieve that? We need to do some sort of rolling calculation in the ROWS parameter of the OFFSET formula. Conceptually, we would want 4, 8, 12, 16, 20, 24, etc. How can we do that while moving your formula down just 1 row at a time (1, 2, 3, 4, 5, 6, etc.)? Multiplication. So find out what row your formula is in (example, =ROW(1:1) returns 1), and multiply that by 4. This will give you 1X4=4, 2x4=8, 3x4=12, 4x4=16, 4x5=20, 4x6=24, etc). Depending on your starting point (row 1 with offset 4), you may want to pull your first value with 0 offset. If you point your REFERENCE to the first desired result, the first instance of your formula would offset down 4. To account for this, subtract 4 from your row formula.

ROW takes
>Referece (cell or row to return the number portion from) Example: 4:4 or B4

Putting it together:

=OFFSET(Source!$A$5,(row(1:1)*4)-4,0)

 

[rlexcel101]

So basically I want for example..

A1=sourcedata$B$4
A4=sourcedata$B$4
A8=sourcedata$B$4

 

[rlexcel101]

So basically I want for example..

A1=sourcedata$B$4
A4=sourcedata$B$4
A8=sourcedata$B$4

Close quotes from offset too early.

OFFSET takes the following
>Starting point (a single cell reference from which you will move. This should be the first item you want to return from the source sheet.) Example: Source!$A$5
>Rows (how many rows you want to move from the original referenced cell. This should be a rolling increment of 4, which will involve some trickery using ROW which I will explain below) Example: 4 (but better if dynamic and increasing, see ROW description)
>Cols (how many rows you want to move from the original referenced cell. You only want to move down, so said your data is vertical, so this should be 0) Example: 0

If you stopped with these inputs as I described, your formula would return the value in cell Source!$A$9 (A5, offset down 4 rows (5+4=9), and offset 0 columns right (A+0=A).

This achieves your desire of offsetting by 4, but doesn't allow you to copy the formula down and keep looking every 4th item. How do we achieve that? We need to do some sort of rolling calculation in the ROWS parameter of the OFFSET formula. Conceptually, we would want 4, 8, 12, 16, 20, 24, etc. How can we do that while moving your formula down just 1 row at a time (1, 2, 3, 4, 5, 6, etc.)? Multiplication. So find out what row your formula is in (example, =ROW(1:1) returns 1), and multiply that by 4. This will give you 1X4=4, 2x4=8, 3x4=12, 4x4=16, 4x5=20, 4x6=24, etc). Depending on your starting point (row 1 with offset 4), you may want to pull your first value with 0 offset. If you point your REFERENCE to the first desired result, the first instance of your formula would offset down 4. To account for this, subtract 4 from your row formula.

ROW takes
>Referece (cell or row to return the number portion from) Example: 4:4 or B4

Putting it together:

=OFFSET(Source!$A$5,(row(1:1)*4)-4,0)

So I copied that exact formula and change the row to reflect the row my formula will be in(first cell is R8)

So I have now:

=OFFSET(source$$,(ROW(8:8),*4),-4,0)

I am just getting zero values for all

 

[gmhumphr]

So I copied that exact formula and change the row to reflect the row my formula will be in(first cell is R8)

So I have now:

=OFFSET(source$$,(ROW(8:8),*4),-4,0)

I am just getting zero values for all

You have one too many commas
You need to point to a single source that never moves

OFFSET(sourcedata!$A$1,(ROW(1:1)-4)*4,0)

Use ROW(1:1) in the very first formula you post, it will increment as you drag it down.

 

[rlexcel101]

You have one too many commas
You need to point to a single source that never moves

OFFSET(sourcedata!$A$1,(ROW(1:1)-4)*4,0)

Use ROW(1:1) in the very first formula you post, it will increment as you drag it down.

So my cell where I am inputting this formula in is R8..

I now have that formula with my source cell frozen completely and I am now getting a value of another cell on the source sheet.

This source value is 125000

I basically need:

R8=125000
R12=125000

And so on

 

[gmhumphr]

I was solving a different problem... Where you had moving data in another workbook and wanted to bring it in to the destination.
Instead you have a single data point that you want to bring in every 4th cell, a simpler problem, pending an answer:

What do you want in R9, R10, R11?

in R8, enter the following formula: =IF(MOD(ROW(8:8),4)=0,sourcedata!$A$5,"")

 

[rlexcel101]

I was solving a different problem... Where you had moving data in another workbook and wanted to bring it in to the destination.
Instead you have a single data point that you want to bring in every 4th cell, a simpler problem, pending an answer:

What do you want in R9, R10, R11?

in R8, enter the following formula: =IF(MOD(ROW(8:8),4)=0,sourcedata!$A$5,"")

I need zero in every other cell.