Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
donesquire
 
Posts: n/a
Default Syntax for inferred cell references

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
  #2   Report Post  
donesquire
 
Posts: n/a
Default

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
  #3   Report Post  
Duke Carey
 
Posts: n/a
Default

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

  #4   Report Post  
donesquire
 
Posts: n/a
Default

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

  #5   Report Post  
donesquire
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I use the contents of a cell to satisfy the result_vector arg. robh_2 Excel Worksheet Functions 3 February 24th 05 09:14 PM
Excel cell references to 2nd worksheet stopped working StardustMM Excel Worksheet Functions 1 February 11th 05 05:31 PM
Can a Formula in Cell X modify Cell Y? alMandragor Excel Discussion (Misc queries) 7 February 10th 05 10:51 PM
Using Indirect en direct cell references Bart Schouw Excel Discussion (Misc queries) 2 January 13th 05 02:05 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 05:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"