Adjust cell reference

[Bongard]

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

 

[Jarek Kujawa]

would this:

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

help?

 

[Dave Peterson]

I don't think so.

=indirect() will return an error if the sending workbook is not open. And based

would this:

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

help?

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

 

[Bongard]

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?

 

[Dave Peterson]

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.)

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

 

[Jarek Kujawa]

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

I don't think so.

=indirect() will return an error if the sending workbook is not open.  And based

would this:

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

help?

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 -

 

[Bongard]

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

Brian