An indirect reference that works

[Robert Peirce]

See previous post.

This is in the 'MONTHLY DATA' sheet. 'DATA'B2 contains the current
number of the last monthly row. I first use that value to determine the
value in the last row and I next use it to annualize the data. The
values are all indexed from an initial value of 1, so I only need the
last value.

=100*(INDIRECT("L"&Data!B2)^(12/(Data!$B$2-3))-1)

=====================================================

The LINEST equation in the DATA sheet is much more difficult.

=INDEX(LINEST('ANNUAL DATA'!C3:C33,'ANNUAL DATA'!B3:B33),2)

Here I want to pick up the 33 from DATA!B3, but sticking an INDIRECT in
there does not work.

 

[John McGhie]

You need a Dynamic Named Range.

See here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Cheers

See previous post.

This is in the 'MONTHLY DATA' sheet. 'DATA'B2 contains the current
number of the last monthly row. I first use that value to determine the
value in the last row and I next use it to annualize the data. The
values are all indexed from an initial value of 1, so I only need the
last value.

=100*(INDIRECT("L"&Data!B2)^(12/(Data!$B$2-3))-1)

=====================================================

The LINEST equation in the DATA sheet is much more difficult.

=INDEX(LINEST('ANNUAL DATA'!C3:C33,'ANNUAL DATA'!B3:B33),2)

Here I want to pick up the 33 from DATA!B3, but sticking an INDIRECT in
there does not work.

--

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[Bob Greenblatt]

I may not have been clear, but this works.


I may be completely obtuse, but I went to that web site and I could not
figure out how that could help me. What I am after is something that
would work the same as

=INDEX(LINEST('ANNUAL DATA'!C3:INDIRECT("C"&Data!B3),'ANNUAL
DATA'!B3:INDIRECT("B"&Data!B3),2)

If that actually worked, which it doesn't. In other words, instead of
using the hardwired value, currently C33, it would append the contents
of Data!B3, which is currently 33 as well, to C, producing C33
indirectly.

It would work if you coded it the way I stated in a prior response. Yes, a
self defining range would also work. Where in PA are you? I'm in NJ. Feel
free to contact me off list and I will be happy to work with you on this.

 

[Robert Peirce]

You need a Dynamic Named Range.

See here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

I may not have been clear, but this works.

I may be completely obtuse, but I went to that web site and I could not
figure out how that could help me. What I am after is something that
would work the same as

=INDEX(LINEST('ANNUAL DATA'!C3:INDIRECT("C"&Data!B3),'ANNUAL
DATA'!B3:INDIRECT("B"&Data!B3),2)

If that actually worked, which it doesn't. In other words, instead of
using the hardwired value, currently C33, it would append the contents
of Data!B3, which is currently 33 as well, to C, producing C33
indirectly.

 

[John McGhie]

Hi Robert:

Maybe you need some sleep

Instead of using "INDIRECT" or INDEX to figure out where the row you want
is, create a Dynamic Range, give it a name, and use the name in the LINEST
formula.

That way, when the data changes, your dynamic range will change and the
formula will always work.

Cheers

I may not have been clear, but this works.


I may be completely obtuse, but I went to that web site and I could not
figure out how that could help me. What I am after is something that
would work the same as

=INDEX(LINEST('ANNUAL DATA'!C3:INDIRECT("C"&Data!B3),'ANNUAL
DATA'!B3:INDIRECT("B"&Data!B3),2)

If that actually worked, which it doesn't. In other words, instead of
using the hardwired value, currently C33, it would append the contents
of Data!B3, which is currently 33 as well, to C, producing C33
indirectly.

--

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[Robert Peirce]

Hi Robert:

Maybe you need some sleep

Instead of using "INDIRECT" or INDEX to figure out where the row you want
is, create a Dynamic Range, give it a name, and use the name in the LINEST
formula.

That way, when the data changes, your dynamic range will change and the
formula will always work.

I'll give it another look. I didn't understand LINEST at first either.
Maybe I just need to play with it.