Adjust cell reference
 

I am trying to adjust a range that is being referenced in a formula.
Here is my formula =STDEV('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$269) The number that
I'd like to be able to adjust is that 269. I have another cell that is
calculating the length of that range (essentially a dynamic range in
another workbook). The rest of the formula would remain the same but
I'd like it to work something like this =STDEV('O:\Shared\BrianB
\AlphaTesting\[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$&J7&)
where cell J7 is calculating a number for the end of the range. Can
this be done?

Thanks a lot,
Brian

Adjust cell reference
 

would this:

=STDEV(INDIRECT('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$"&J7))

help?


On 19 Mar, 13:08, Bongard wrote:
I am trying to adjust a range that is being referenced in a formula.
Here is my formula =STDEV('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$269) The number that
I'd like to be able to adjust is that 269. I have another cell that is
calculating the length of that range (essentially a dynamic range in
another workbook). The rest of the formula would remain the same but
I'd like it to work something like this =STDEV('O:\Shared\BrianB
\AlphaTesting\[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$&J7&)
where cell J7 is calculating a number for the end of the range. Can
this be done?

Thanks a lot,
Brian

Adjust cell reference
 

I don't think so.

=indirect() will return an error if the sending workbook is not open. And based
on the drive and path, I'd bet that this file isn't open.

Jarek Kujawa wrote:

would this:

=STDEV(INDIRECT('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$"&J7))

help?

On 19 Mar, 13:08, Bongard wrote:
I am trying to adjust a range that is being referenced in a formula.
Here is my formula =STDEV('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$269) The number that
I'd like to be able to adjust is that 269. I have another cell that is
calculating the length of that range (essentially a dynamic range in
another workbook). The rest of the formula would remain the same but
I'd like it to work something like this =STDEV('O:\Shared\BrianB
\AlphaTesting\[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$&J7&)
where cell J7 is calculating a number for the end of the range. Can
this be done?

Thanks a lot,
Brian

--

Dave Peterson

Adjust cell reference
 

Jarek thank you for your response, but that doesn't work. It tells me
that the formula contains an error. Dave you are correct, the workbook
will not be open, which is why I don't refer to a dynamic range in the
other spreadsheet. Is there no other way to adjust this cell reference
without using indirect?

Adjust cell reference
 

Maybe you can use =index():

=stdev('o:\...-q'!bk5:index('o\...-q'!bk5:bk10000:5+j7))

I'd test it with that sending workbook open to make it easier to get that syntax
right. Then close the sending file to see if it works.

(You may want 4+j7 and don't forget to use absolute references if you want
them.)



Bongard wrote:

I am trying to adjust a range that is being referenced in a formula.
Here is my formula =STDEV('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$269) The number that
I'd like to be able to adjust is that 269. I have another cell that is
calculating the length of that range (essentially a dynamic range in
another workbook). The rest of the formula would remain the same but
I'd like it to work something like this =STDEV('O:\Shared\BrianB
\AlphaTesting\[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$&J7&)
where cell J7 is calculating a number for the end of the range. Can
this be done?

Thanks a lot,
Brian

--

Dave Peterson

Adjust cell reference
 

yep, I thought of that too
but the OP never mentioned it
or I missed that


On 19 Mar, 13:50, Dave Peterson wrote:
I don't think so.

=indirect() will return an error if the sending workbook is not open. *And based
on the drive and path, I'd bet that this file isn't open.





Jarek Kujawa wrote:

would this:

=STDEV(INDIRECT('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$"&J7))

help?

On 19 Mar, 13:08, Bongard wrote:
I am trying to adjust a range that is being referenced in a formula.
Here is my formula =STDEV('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$269) The number that
I'd like to be able to adjust is that 269. I have another cell that is
calculating the length of that range (essentially a dynamic range in
another workbook). The rest of the formula would remain the same but
I'd like it to work something like this =STDEV('O:\Shared\BrianB
\AlphaTesting\[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$&J7&)
where cell J7 is calculating a number for the end of the range. Can
this be done?

Thanks a lot,
Brian

--

Dave Peterson- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Adjust cell reference
 

No luck here. I may look another route to try and accomplish the
dynamic external cell references. Thanks for your help guys.

Brian