Adjust cell reference

  • Thread starter Thread starter Bongard
  • Start date Start date
B

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
 
would this:

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

help?
 
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
Click to expand...
Click to expand...
 
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?
 
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
Click to expand...
 
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:
Click to expand...
=STDEV(INDIRECT('O:\Shared\BrianB\AlphaTesting\
[PriceToSales_Download.xls]BANKS - Q'!$BK$5:$BK$"&J7))
help?
Click to expand...

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
Click to expand...
Click to expand...

--

Dave Peterson- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -
Click to expand...
 
No luck here. I may look another route to try and accomplish the
dynamic external cell references. Thanks for your help guys.

Brian
 
Back
Top