I have a formula that calculates a trailing 12 month average return by
dividing a cumulative return from purchase date by the cumulative return from
12 months before. The cell reference for the return from purchase date is
always the same. But the cell reference for the return 12 months before
changes each month. The row # where the "today - 12" months return is
located is calculated in a separate cell. Is it possible for my 12 month
trailing average formula to calculate the cell reference by looking at the
row number calculated in this other cell? Here are some details that may
clarify:

- Cell D6 on "Main" sheet contains an overall cumulative return number
- Sheet "Cumulative data" contains a column of cumulative monthly returns
that is updated by added a new month to the bottom of the column each
successive month
- Cell B33 on "Main" sheet contains the row # of the "Cumulative data" sheet
with the cumulative return calculated as of the current month
- Cell D33 on main sheet contains the trailing 12 month return discussed.
It's formula is:
=((D6+1)/('Cumulative data'!D75+1))-1
- Cell reference "D75" in this formula is changed every month to the next
row, e.g., "D76," to account for the new return added. The row # "76" can be
found in Cell B33 of the main sheet.
- So the Question is: How do I get the row # calculated in Cell B33 into
the formula in Cell D33 so I don't have to mannually change it every month?

Any help is appreciated,
Don

I think my original question was too complex. Here's a boiled down version.

I want a formula in a cell to calculate a cell reference based on a number
in a different cell. For example:

- Cell B7 contains the value 90
- Cell D7 contains the formula: ='Cumulative data'D90
(Cumulative data is a sheet name)
- the row "90" called in the formula needs to be inserted by referring to
the value of cell B7.

Is there any way for me to do that?

Any help is appreciated,
Don

How about

=INDIRECT("'Cumulative data'D"&B7)


"donesquire" wrote:

I think my original question was too complex. Here's a boiled down version.

I want a formula in a cell to calculate a cell reference based on a number
in a different cell. For example:

- Cell B7 contains the value 90
- Cell D7 contains the formula: ='Cumulative data'D90
(Cumulative data is a sheet name)
- the row "90" called in the formula needs to be inserted by referring to
the value of cell B7.

Is there any way for me to do that?

Any help is appreciated,
Don

I think this puts me on the right track although the syntax you suggest
yields a "#REF!" error.

I was unfamiliar with the "INDIRECT" function. But the help screens seem to
indicate that it could do what I want if I can find the right syntax.

Thanks for your help!


"Duke Carey" wrote:

How about

=INDIRECT("'Cumulative data'D"&B7)


"donesquire" wrote:

I think my original question was too complex. Here's a boiled down version.

I want a formula in a cell to calculate a cell reference based on a number
in a different cell. For example:

- Cell B7 contains the value 90
- Cell D7 contains the formula: ='Cumulative data'D90
(Cumulative data is a sheet name)
- the row "90" called in the formula needs to be inserted by referring to
the value of cell B7.

Is there any way for me to do that?

Any help is appreciated,
Don

The syntax was missing the exclamation mark "!" after the sheet name. With
the correction the formula does exactly what I need it to do.

Thanks again!


"donesquire" wrote:

I think this puts me on the right track although the syntax you suggest
yields a "#REF!" error.

I was unfamiliar with the "INDIRECT" function. But the help screens seem to
indicate that it could do what I want if I can find the right syntax.

Thanks for your help!


"Duke Carey" wrote:

How about

=INDIRECT("'Cumulative data'D"&B7)


"donesquire" wrote:

I think my original question was too complex. Here's a boiled down version.

I want a formula in a cell to calculate a cell reference based on a number
in a different cell. For example:

- Cell B7 contains the value 90
- Cell D7 contains the formula: ='Cumulative data'D90
(Cumulative data is a sheet name)
- the row "90" called in the formula needs to be inserted by referring to
the value of cell B7.

Is there any way for me to do that?

Any help is appreciated,
Don