Re: Reference every 13th cell from another tab. by Roger
Roger
Tue Mar 18 10:57:08 CDT 2008
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.
--
Regards
Roger Govier
"jordanpcpre" <jordanpcpre@discussions.microsoft.com> wrote in message
news:F645A190-F89C-4526-84A8-1C70CB8DE264@microsoft.com...
> 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" wrote:
>
>> 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)))
>>
>>
>> --
>> Regards
>> Roger Govier
>>
>> "jordanpcpre" <jordanpcpre@discussions.microsoft.com> wrote in message
>> news:A2626050-3456-4BE0-AE1C-CEC857ABA678@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >> Regards
>> >> Roger Govier
>> >>
>> >> "jordanpcpre" <jordanpcpre@discussions.microsoft.com> wrote in message
>> >> news:1315BADF-5872-41EC-953B-E269A9809F1C@microsoft.com...
>> >> > 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.
>> >>