Problems with R1C1 referencing in buildling indirect references
 

I am using indirect to check for a range predetermined by using labels and
CONCATENATE. I want to create the formula so that when columns are added,
the least amount of maintenance is needed, so I have:
=COUNTIF(INDIRECT(I178),"<N/A")

with INDIRECT referring to:

=CONCATENATE("I",FIRST_TEST_ROW,":I",LAST_TEST_ROW )

where I is the column.

THIS WORKS FINE, BUT:

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?
How do I get my low maintenance solution if not this way (which should be
really elegant)?

Problems with R1C1 referencing in buildling indirect references
 

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_TES T_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_TE ST_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")

Problems with R1C1 referencing in buildling indirect reference
 

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_TES T_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_TE ST_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")

Problems with R1C1 referencing in buildling indirect reference
 

I think you have already found your answer, but here is an alternate method I
have used in the past. I search the column labels in the first row, saving
the column number of the columns I need. This allows additional columns to
be added or deleted, without effecting any of the code relating to these key
columns.
Just set the ending range in sufficiently past your data so that several
columns can be inserted (or set end of range to "ZZ" and exit loop on
xCell.value = "")


'Find the column numbers of the data that needs searched
For Each xCell In ActiveWorkbook.Sheets(sheetName).Range("A1:Q1")
strFieldName = xCell.Value

Select Case strFieldName

Case "ContactName": colDistributor = xCell.Column
Case "PricingID": colPricingID = xCell.Column
Case "% of Biz": colPercentOfBiz = xCell.Column
Case "Program $": colProgramDollars = xCell.Column

End Select




"IainMac" wrote:

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_TES T_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_TE ST_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")