I want to chage the row selection on a function by using a cell reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

Hi Farrel,

try this:

=INDIRECT("A"&B2)

Regards,
KL

"Farrel" wrote in message
...
I want to chage the row selection on a function by using a cell reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

Hi,

Try =INDIRECT("A"&B2) which gives you the value in the cell A5. I think
that's what you mean.

HTH

David

"Farrel" wrote:

I want to chage the row selection on a function by using a cell reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

Hey txs for the Help
but why =SUBTOTAL(1,Sheet2!C3:Sheet2!(INDIRECT("c"&Sheet2! B1)))
doest work?

"KL" wrote:

Hi Farrel,

try this:

=INDIRECT("A"&B2)

Regards,
KL

"Farrel" wrote in message
...
I want to chage the row selection on a function by using a cell reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

this should work:

=SUBTOTAL(1,INDIRECT("Sheet2!C3:C"&Sheet2!B1))

KL


"Farrel" wrote in message
...
Hey txs for the Help
but why =SUBTOTAL(1,Sheet2!C3:Sheet2!(INDIRECT("c"&Sheet2! B1)))
doest work?

"KL" wrote:

Hi Farrel,

try this:

=INDIRECT("A"&B2)

Regards,
KL

"Farrel" wrote in message
...
I want to chage the row selection on a function by using a cell
reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

....or
=SUBTOTAL(1,INDIRECT("Sheet2!"&CELL("address",C3)& ":C"&Sheet2!B1))




"KL" wrote in message
...
this should work:

=SUBTOTAL(1,INDIRECT("Sheet2!C3:C"&Sheet2!B1))

KL


"Farrel" wrote in message
...
Hey txs for the Help
but why =SUBTOTAL(1,Sheet2!C3:Sheet2!(INDIRECT("c"&Sheet2! B1)))
doest work?

"KL" wrote:

Hi Farrel,

try this:

=INDIRECT("A"&B2)

Regards,
KL

"Farrel" wrote in message
...
I want to chage the row selection on a function by using a cell
reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

Damn! you're good !!!
TXS

"KL" wrote:

this should work:

=SUBTOTAL(1,INDIRECT("Sheet2!C3:C"&Sheet2!B1))

KL


"Farrel" wrote in message
...
Hey txs for the Help
but why =SUBTOTAL(1,Sheet2!C3:Sheet2!(INDIRECT("c"&Sheet2! B1)))
doest work?

"KL" wrote:

Hi Farrel,

try this:

=INDIRECT("A"&B2)

Regards,
KL

"Farrel" wrote in message
...
I want to chage the row selection on a function by using a cell
reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS

Can be set up in a less expensive way:

=SUBTOTAL(1,Sheet2!$C$3:INDEX(Sheet2!$C:$C,B1))

KL wrote:
...or
=SUBTOTAL(1,INDIRECT("Sheet2!"&CELL("address",C3)& ":C"&Sheet2!B1))




"KL" wrote in message
...

this should work:

=SUBTOTAL(1,INDIRECT("Sheet2!C3:C"&Sheet2!B1))

KL


"Farrel" wrote in message
...

Hey txs for the Help
but why =SUBTOTAL(1,Sheet2!C3:Sheet2!(INDIRECT("c"&Sheet2! B1)))
doest work?

"KL" wrote:


Hi Farrel,

try this:

=INDIRECT("A"&B2)

Regards,
KL

"Farrel" wrote in message
...

I want to chage the row selection on a function by using a cell
reference
"A(B2)"
so when I change "B2" to 5
the function will read "A5"
TXS







--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.