Re: Problems with R1C1 referencing in buildling indirect reference

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: IainMac (IainMac_at_discussions.microsoft.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 10:31:07 -0800

Thanks - though as the function is in each repeaed row, I will keep the
volatile bit...
... D'oh - there it is indeed in the manual!

"Harlan Grove" wrote:

> IainMac wrote...
> ....
> >I want to minimise maintenance. As it stands, if someone else works
> with
> >the sheet, they have to know to copy and paste a column, then change
> the
> >column references to match the new column.
> >
> >I decided to try R1C1 referencing, to avoid explicitly reference the
> column:
> >
> >=COUNTIF(INDIRECT(R[31]C),"<>N/A")
> >
> >with INDIRECT referring to:
> >
> >=CONCATENATE("R",FIRST_TEST_ROW,"C",":R",LAST_TEST_ROW,"C")
> >
> >MAYHEM:
> >When I do this (whether I switch R1C1 referencing on or off), I get
> #REF as
> >a result for the formula. The CONCATENATION still looks ok
> :"R12C:R144C"
> >
> >What is going wrong?
>
> Simple. You failed to read the manual. Online help for the INDIRECT
> function it states:
>
> "INDIRECT(ref_text,a1)
> [...]
> A1 is a logical value that specifies what type of reference is
> contained in the cell ref_text.
>
> · If a1 is TRUE or omitted, ref_text is interpreted as an A1-style
> reference.
> · If a1 is FALSE, ref_text is interpreted as an R1C1-style reference."
>
> Where does it say the a1 argument is optional when using R1C1-style
> references?
>
> >How do I get my low maintenance solution if not this way (which should
> be
> >really elegant)?
>
> If you want elegance, don't use CONCATENATE. Use the & operator.
>
> =COUNTIF(INDIRECT("R"&FIRST_TEST_ROW&"C:R"&LAST_TEST_ROW&"C",0),"<>N/A")
>
> And if you want to avoid volatile functions and this function would be
> in column I, consider
>
> =COUNTIF(INDEX(I:I,FIRST_TEST_ROW):INDEX(I:I,LAST_TEST_ROW),"<>N/A")
>
>

Relevant Pages

  • Re: Problems with R1C1 referencing in buildling indirect references
    ... >column references to match the new column. ... >with INDIRECT referring to: ... The CONCATENATION still looks ok ... >really elegant)? ...
    (microsoft.public.excel.programming)
  • Re: Thread safety and atomic assignment (again)
    ... By searching the web I found lots of references about the below question. ... Lea or Bloch) about it. ... the lack of volatile may cause setValues in thread to not be ... Doug Lea also asserts on p.93 that "atomicity extends to volatilie long and ...
    (comp.lang.java.programmer)
  • Re: ifort volatile with O3
    ... optimizations a compiler might perform, but the Note at least warns ... "The VOLATILE attribute specifies that an object may be referenced, ... it can't specify the timing of memory references. ...
    (comp.lang.fortran)
  • Re: need help finding a "possible" deadlock in simple application...
    ... threaded environment, which would justify your code, please let me ... references to volatile objects as well as references to other global ... It only covers C compiled by the Visual C++ compiler, ...
    (comp.programming.threads)
  • Re: Newbie question: accessing global variable on multiprocessor
    ... > memory barrier at all on VC++2005. ... to volatile objects as well as references to other global objects. ... These architectures do not reorder read operations followed by read operations or write operations followed by write operations. ...
    (comp.programming.threads)