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
"0-0 Wai Wai ^-^" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>> 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
>>
>> "0-0 Wai Wai ^-^" <(E-Mail Removed)> wrote in message
>> news:uVkP$(E-Mail Removed)...
>> >
>> >> 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
>> >>
>> >> "0-0 Wai Wai ^-^" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >
>> >> > 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?
>> >> >
>> >> >
>> >> > --
>> >> > Additional information:
>> >> > - I'm using Office XP
>> >> > - I'm using Windows XP
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
Similar Threads
