Reference every 13th cell from another tab.

[jordanpcpre]

Is there a formula that will reference a cell in a seperate tab for every
13th cell.

For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be
13 rows below =+Equity!E166...

So, R7 equals =+Equity!E166
S7 equals =+Equity!E179
T7 equals =+Equity!E192

I need to repeat this for multiple colums.

 

[Chip Pearson]

You can use the INDIRECT function. For example,

=INDIRECT("Equity!E"&166+(COLUMN()-18)*13)

Here, change the "E" to the column letter from which to pull the values from
the Equity sheet, change 166 to the first row on Equity from which data
should be pulled, change the 18 to the column number of the first cell in
which the formula appears (in this case, 18 = column R), and change the 13
to the number of rows to skip when pulling data off the Equity sheet. Then
select the cells that are to contain the results and CTRL R to fill to the
right.

Rather than hard-coding these values in to the formula, you might want to
put the values in cells and reference those cells in the formula.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Roger Govier]

Hi

One way would be to use 2 cells on your sheet to hold the starting row
number (166) and the offset (13). lets say these are in cells R1 and R2
respectively.

in cell R7
=INDEX(+Equity!$E:$E,$R$1+($R$2*(COLUMN(A1)-1)))
Copy across as far as required

 

[jordanpcpre]

Here is the formula that I have inputed from the information you provided;
however I am still getting a #NUM! error.

=INDEX(+Equity!$E:$E,$E127+($E$13*(COLUMN()-5)))

I'm in the 'IRR' tab working in row 5, and need to reference cells in the
'Equity' tab. I need to reference Equity!E127, and then every 13th row (in
column e) after that. I would like to be able to drag this formula across
row 5 (in the IRR tab) and reference every 13th row (in the Equity tab) in
column E.

Thank you for the help!

 

[Roger Govier]

Hi
You don't need the column references. We are only dealing with numbers.
If you are going to hard code the numbers into the formula, rather than
holding them in other cells, then use
=INDEX(+Equity!$E:$E,127+(13*(COLUMN()-5)))

 

[jordanpcpre]

Thank you for the help Roger. We almost have it.

My current formula in cell IRR!L7:
=INDEX(Equity!$E:$E,127+(COLUMN( )-5)*13)

I am in cell L7 of the IRR tab trying to reference cell E127 in the Equity
tab. Should the above formula reference the Equity!E127 cell correctly? I
would then like to drag this formula one column to the right and have it
reference automatically 13 rows below Equity!E127 (so IRR!M7 should reference
Equity!E140).

I would like to drag this formula to the right as far as necessary.

Thanks! We almost have it!

 

[Roger Govier]

Hi

Then go back to almost how I had sent the first formula to you, but
substituting your fixed numeric values in place of the cell references.
=INDEX(Equity!$E:$E,127+(COLUMN(A1)-1)*13)

Column(A1) will return 1, whatever cell the formula is entered into, so
COLUMN(A1)-1 will be 0, and multiplying 13*0 = 0 so there will be no
addition to the starting row number (127)
As you copy across the page, COLUMN(A1) will become COLUMN(B1) etc, so the
add on to 127 will become 1*13, 2*13 etc.