"Dynamic cell reference" problems

[0-0 Wai Wai ^-^]

Hi.
I encounter a problem when I do the following:

There're many cells which use the same cell references:
A101 =Countif(A1:A100, ">5")
A102 =sum(A1:A100)
and so on

I wish to put the reference "A1:A100" & ">5" in other cells, so when these
values change, all formulas which uses these values will be dynamically updated.

What I do is putting the following cell contents in different cells:
S1 = INDIRECT(T1&":"&T2)
- T1 = A1 (ie in T1 cell, I type in [A1] {without brackets})
- T2 = A100
S2 = >5

Then I can dynamically linked by rewriting different formulas:
A101 =Countif(S1, S2)
A102 =sum(S1)

But it won't work. (error: #REF!)
What's wrong?

 

[Bob Umlas]

Put the indirect directly into the countif:
=COUNTIF(INDIRECT(T1&":"&T2),S2)
=SUM(INDIRECT(T1&":"&T2))
because S1 contains INDIRECT(T1&":"&T2) and that may be 0 and INDIRECT(S1)
is INDIRECT(0) which doesn't mean anything.
Bob Umlas
Excel MVP

 

[0-0 Wai Wai ^-^]

Put the indirect directly into the countif:
=COUNTIF(INDIRECT(T1&":"&T2),S2)
=SUM(INDIRECT(T1&":"&T2))

Is it the only way to make it work?
It's because I would like to put it elsewhere (for easy updating purpose).
If I put something like INDIRECT(T1) instead, I do not need to put this formula
straight to the COUNTIF function.

because S1 contains INDIRECT(T1&":"&T2) and that may be 0 and INDIRECT(S1)
is INDIRECT(0) which doesn't mean anything.

Sorry for my ignorance.
I wonder if INDIRECT(T1&":"&T2) should read like that:

- INDIRECT(T1&":"&T2)
- INDIRECT(A1:A100) [Remember T1 references to A1 etc.]

Why does it read as INDIRECT(S1)??
Thanks for your help.

 

[Earl Kiosterud]

Wai,

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't work.
You can't tell it to set one cell to a range of values -- they don't fit.
You need something that takes the range argument A1:A100, like your COUNTIF:
=COUNTIF(INDIRECT(T1&":"&T2),S2)

Or use a defined name. If MyRange is defined (Insert-Name - Define) as
=INDIRECT(Sheet1!$T$1&":"&Sheet1!$T$2)

Then you could write:
=COUNTIF(MyRange, S2)

 

[0-0 Wai Wai ^-^]

Then I can dynamically linked by rewriting different formulas:
A101 =Countif(S1, S2)
A102 =sum(S1)

But it won't work. (error: #REF!)
What's wrong?

The error type should be #VALUE!, not #REF!

 

[0-0 Wai Wai ^-^]

Wai,

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't work.
You can't tell it to set one cell to a range of values -- they don't fit.

Do you mean this function is able to return 1 value only but no more?

You need something that takes the range argument A1:A100, like your COUNTIF:
=COUNTIF(INDIRECT(T1&":"&T2),S2)

Or use a defined name. If MyRange is defined (Insert-Name - Define) as
=INDIRECT(Sheet1!$T$1&":"&Sheet1!$T$2)

Then you could write:
=COUNTIF(MyRange, S2)

You are awesome!
So I can use name instead to deal with this problem.
By the way, if I wsih to redefine my range, how to do?

--
Earl Kiosterud
www.smokeylake.com

Hi.
I encounter a problem when I do the following:

There're many cells which use the same cell references:
A101 =Countif(A1:A100, ">5")
A102 =sum(A1:A100)
and so on

I wish to put the reference "A1:A100" & ">5" in other cells, so when these
values change, all formulas which uses these values will be dynamically
updated.

What I do is putting the following cell contents in different cells:
S1 = INDIRECT(T1&":"&T2)
- T1 = A1 (ie in T1 cell, I type in [A1] {without brackets})
- T2 = A100
S2 = >5

Then I can dynamically linked by rewriting different formulas:
A101 =Countif(S1, S2)
A102 =sum(S1)

But it won't work. (error: #REF!)
What's wrong?

 

[Earl Kiosterud]

Wai Wai,

To redefine a name, Insert - Name - Define. Select the name. The "refers
to" can now be edited or replaced. In Excel 2002 you can finish with OK.
I'm not sure, but in earlier versions, you might have to click Add first.
Perhaps someone will comment.

Note that the Refers to box is a RefEdit box. That means you can click or
drag in the worksheet, and it will put the corresponding cell reference
where the cursor is. You don't have to type the cell references.
--
Earl Kiosterud
www.smokeylake.com

Wai,

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't
work.
You can't tell it to set one cell to a range of values -- they don't fit.

Do you mean this function is able to return 1 value only but no more?

You need something that takes the range argument A1:A100, like your
COUNTIF:
=COUNTIF(INDIRECT(T1&":"&T2),S2)

Or use a defined name. If MyRange is defined (Insert-Name - Define) as
=INDIRECT(Sheet1!$T$1&":"&Sheet1!$T$2)

Then you could write:
=COUNTIF(MyRange, S2)

You are awesome!
So I can use name instead to deal with this problem.
By the way, if I wsih to redefine my range, how to do?

--
Earl Kiosterud
www.smokeylake.com

Hi.
I encounter a problem when I do the following:

There're many cells which use the same cell references:
A101 =Countif(A1:A100, ">5")
A102 =sum(A1:A100)
and so on

I wish to put the reference "A1:A100" & ">5" in other cells, so when
these
values change, all formulas which uses these values will be dynamically
updated.

What I do is putting the following cell contents in different cells:
S1 = INDIRECT(T1&":"&T2)
- T1 = A1 (ie in T1 cell, I type in [A1] {without brackets})
- T2 = A100
S2 = >5

Then I can dynamically linked by rewriting different formulas:
A101 =Countif(S1, S2)
A102 =sum(S1)

But it won't work. (error: #REF!)
What's wrong?

 

[0-0 Wai Wai ^-^]

Wai Wai,

To redefine a name, Insert - Name - Define. Select the name. The "refers
to" can now be edited or replaced. In Excel 2002 you can finish with OK.
I'm not sure, but in earlier versions, you might have to click Add first.
Perhaps someone will comment.

Note that the Refers to box is a RefEdit box. That means you can click or
drag in the worksheet, and it will put the corresponding cell reference
where the cursor is. You don't have to type the cell references.

Thanks.
One question left:

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't work.
You can't tell it to set one cell to a range of values -- they don't fit.

Do you mean this function is able to return 1 value only but no more?
Thanks for your answer.

--
Earl Kiosterud
www.smokeylake.com

Wai,

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't
work.
You can't tell it to set one cell to a range of values -- they don't fit.

Do you mean this function is able to return 1 value only but no more?

You need something that takes the range argument A1:A100, like your
COUNTIF:
=COUNTIF(INDIRECT(T1&":"&T2),S2)

Or use a defined name. If MyRange is defined (Insert-Name - Define) as
=INDIRECT(Sheet1!$T$1&":"&Sheet1!$T$2)

Then you could write:
=COUNTIF(MyRange, S2)

You are awesome!
So I can use name instead to deal with this problem.
By the way, if I wsih to redefine my range, how to do?

--
Earl Kiosterud
www.smokeylake.com


Hi.
I encounter a problem when I do the following:

There're many cells which use the same cell references:
A101 =Countif(A1:A100, ">5")
A102 =sum(A1:A100)
and so on

I wish to put the reference "A1:A100" & ">5" in other cells, so when
these
values change, all formulas which uses these values will be dynamically
updated.

What I do is putting the following cell contents in different cells:
S1 = INDIRECT(T1&":"&T2)
- T1 = A1 (ie in T1 cell, I type in [A1] {without brackets})
- T2 = A100
S2 = >5

Then I can dynamically linked by rewriting different formulas:
A101 =Countif(S1, S2)
A102 =sum(S1)

But it won't work. (error: #REF!)
What's wrong?

 

[Earl Kiosterud]

Wai,

Yes. INDIRECT(T1&":"&T2) will return one value from A1:A100, if it happens
to be in the same row as the formula, otherwise, it will return 0. A cell
can contain only one value. A formula in a cell can return only one value.
The eqivalent formula =A1:A100 will behave in the same way.

As a side note, instead of =A1:A100, consider =SUM(A1:A100). This formula
returns one value, the yield of the SUM function, which is the sum of values
in A1:A100. So this formula will work in any location. Similar results for
=COUNT(A1:A100), etc.
--
Earl Kiosterud
www.smokeylake.com

Wai Wai,

To redefine a name, Insert - Name - Define. Select the name. The
"refers
to" can now be edited or replaced. In Excel 2002 you can finish with OK.
I'm not sure, but in earlier versions, you might have to click Add first.
Perhaps someone will comment.

Note that the Refers to box is a RefEdit box. That means you can click
or
drag in the worksheet, and it will put the corresponding cell reference
where the cursor is. You don't have to type the cell references.

Thanks.
One question left:

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't
work.
You can't tell it to set one cell to a range of values -- they don't fit.

Do you mean this function is able to return 1 value only but no more?
Thanks for your answer.

--
Earl Kiosterud
www.smokeylake.com

Wai,

Your S1: INDIRECT(T1&":"&T2) is equivalent to =A1:A100, which doesn't
work.
You can't tell it to set one cell to a range of values -- they don't
fit.
Do you mean this function is able to return 1 value only but no more?

You need something that takes the range argument A1:A100, like your
COUNTIF:
=COUNTIF(INDIRECT(T1&":"&T2),S2)

Or use a defined name. If MyRange is defined (Insert-Name - Define)
as
=INDIRECT(Sheet1!$T$1&":"&Sheet1!$T$2)

Then you could write:
=COUNTIF(MyRange, S2)

You are awesome!
So I can use name instead to deal with this problem.
By the way, if I wsih to redefine my range, how to do?

--
Earl Kiosterud
www.smokeylake.com


Hi.
I encounter a problem when I do the following:

There're many cells which use the same cell references:
A101 =Countif(A1:A100, ">5")
A102 =sum(A1:A100)
and so on

I wish to put the reference "A1:A100" & ">5" in other cells, so when
these
values change, all formulas which uses these values will be
dynamically
updated.

What I do is putting the following cell contents in different cells:
S1 = INDIRECT(T1&":"&T2)
- T1 = A1 (ie in T1 cell, I type in [A1] {without brackets})
- T2 = A100
S2 = >5

Then I can dynamically linked by rewriting different formulas:
A101 =Countif(S1, S2)
A102 =sum(S1)

But it won't work. (error: #REF!)
What's wrong?