Extracting a column from a named range

Dear all,

I have named a range "Scores" across 3 sheets (i.e.
Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in "Scores"
in Sheet1 ONLY? I don't know how to provide the target range in the function
MEDIAN().

Best Regards,
Andy Chan

On Sat, 24 Dec 2005 19:24:06 +0800, "Andy Chan"
<[email protected]> wrote:

>Dear all,
>
> I have named a range "Scores" across 3 sheets (i.e.
>Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in "Scores"
>in Sheet1 ONLY? I don't know how to provide the target range in the function
>MEDIAN().
>
>Best Regards,
>Andy Chan
>


=MEDIAN(Sheet3:Sheet1!A1:B10)


--ron

You won't be able to use the name if it spans many sheets, you will have to
use the cell references

=MEDIAN(Sheet1!$A$1:$B$10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andy Chan" <[email protected]> wrote in message
news:[email protected]...
> Dear all,
>
> I have named a range "Scores" across 3 sheets (i.e.
> Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in "Scores"
> in Sheet1 ONLY? I don't know how to provide the target range in the
function
> MEDIAN().
>
> Best Regards,
> Andy Chan
>
>

Dear Bob,

Why not? I just follow the instruction here:

http://office.microsoft.com/en-us/as...015401033.aspx

It works. However, I don't know how to select the first "column".

Best Regards,
Andy


"Bob Phillips" <[email protected]> 撰寫於郵件新聞:[email protected]...
> You won't be able to use the name if it spans many sheets, you will have
> to
> use the cell references
>
> =MEDIAN(Sheet1!$A$1:$B$10)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Andy Chan" <[email protected]> wrote in message
> news:[email protected]...
>> Dear all,
>>
>> I have named a range "Scores" across 3 sheets (i.e.
>> Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in
>> "Scores"
>> in Sheet1 ONLY? I don't know how to provide the target range in the
> function
>> MEDIAN().
>>
>> Best Regards,
>> Andy Chan
>>
>>
>
>

On Sat, 24 Dec 2005 07:54:52 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Sat, 24 Dec 2005 19:24:06 +0800, "Andy Chan"
><[email protected]> wrote:
>
>>Dear all,
>>
>> I have named a range "Scores" across 3 sheets (i.e.
>>Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in "Scores"
>>in Sheet1 ONLY? I don't know how to provide the target range in the function
>>MEDIAN().
>>
>>Best Regards,
>>Andy Chan
>>
>
>
>=MEDIAN(Sheet3:Sheet1!A1:B10)
>
>
>--ron

I neglected to mention that you can define scores as that cell reference:

scores =Sheet3:Sheet1!$A$1:$B$10


--ron

I only want to compute the median of the data in Sheet1 only... But I want
to use the name of the range (Scores)... How can I do it?


"Ron Rosenfeld" <[email protected]>
???????:[email protected]...
> On Sat, 24 Dec 2005 07:54:52 -0500, Ron Rosenfeld
> <[email protected]>
> wrote:
>
>>On Sat, 24 Dec 2005 19:24:06 +0800, "Andy Chan"
>><[email protected]> wrote:
>>
>>>Dear all,
>>>
>>> I have named a range "Scores" across 3 sheets (i.e.
>>>Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in
>>>"Scores"
>>>in Sheet1 ONLY? I don't know how to provide the target range in the
>>>function
>>>MEDIAN().
>>>
>>>Best Regards,
>>>Andy Chan
>>>
>>
>>
>>=MEDIAN(Sheet3:Sheet1!A1:B10)
>>
>>
>>--ron
>
> I neglected to mention that you can define scores as that cell reference:
>
> scores =Sheet3:Sheet1!$A$1:$B$10
>
>
> --ron

I'm sure it is possible one way or another.
But i do find you get yourself into trouble by choosing a rather complex way
to name a range and then want to use that name but mean something else.
The logic escapes me.
Can you explain what you're trying to do which makes this necessay?

--
Kind regards,

Niek Otten

"Andy Chan" <[email protected]> wrote in message
news:[email protected]...
>I only want to compute the median of the data in Sheet1 only... But I want
>to use the name of the range (Scores)... How can I do it?
>
>
> "Ron Rosenfeld" <[email protected]>
> ???????:[email protected]...
>> On Sat, 24 Dec 2005 07:54:52 -0500, Ron Rosenfeld
>> <[email protected]>
>> wrote:
>>
>>>On Sat, 24 Dec 2005 19:24:06 +0800, "Andy Chan"
>>><[email protected]> wrote:
>>>
>>>>Dear all,
>>>>
>>>> I have named a range "Scores" across 3 sheets (i.e.
>>>>Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in
>>>>"Scores"
>>>>in Sheet1 ONLY? I don't know how to provide the target range in the
>>>>function
>>>>MEDIAN().
>>>>
>>>>Best Regards,
>>>>Andy Chan
>>>>
>>>
>>>
>>>=MEDIAN(Sheet3:Sheet1!A1:B10)
>>>
>>>
>>>--ron
>>
>> I neglected to mention that you can define scores as that cell reference:
>>
>> scores =Sheet3:Sheet1!$A$1:$B$10
>>
>>
>> --ron
>
>

On Sat, 24 Dec 2005 23:06:05 +0800, "Andy Chan"
<[email protected]> wrote:

>I only want to compute the median of the data in Sheet1 only... But I want
>to use the name of the range (Scores)... How can I do it?
>
>

If I understand you correctly, you wish to use the name Scores on each of
several sheets, and have it refer the that sheet only.

One way to do this is to enter three names:

Navigate to Sheet1 and then define a name:

Instead of NAMEing as Scores, use the NAME Sheet1!Scores and set it equal to
A1:B10.

Then navigate to Sheet2 and define a name as Sheet2!Scores referring to
Sheet2!A1:B10.

The same for Sheet3.

If you are on the 'native' sheet, you can refer to just Scores -- e.g
=MEDIAN(Scores).

If you are on, let us say, Sheet1 and you want to refer to sheet3, you could
use the formula: =MEDIAN(Sheet3!Scores)


--ron

Dear Niek,

My situation is like this: I want to maintain a file containing
information of 80 classes of students, the information of students of each
class is contained in one sheet. Every sheet has a similar structure, say
columns A to I are for name, gender, scores of each subject, etc. For
example, F1:F20 of each sheet contain scores of math exams of the
corresponding class. I want to find the 80 medians of scores of math exams,
as well as the overall median. Therefore, I want to name these ranges. Is it
clear? Thanks in advance!

Best Regards,
Andy

"Niek Otten" <[email protected]> 撰寫於郵件新聞:[email protected]...
> I'm sure it is possible one way or another.
> But i do find you get yourself into trouble by choosing a rather complex
> way to name a range and then want to use that name but mean something
> else.
> The logic escapes me.
> Can you explain what you're trying to do which makes this necessay?
>
> --
> Kind regards,
>
> Niek Otten
>
> "Andy Chan" <[email protected]> wrote in message
> news:[email protected]...
>>I only want to compute the median of the data in Sheet1 only... But I want
>>to use the name of the range (Scores)... How can I do it?
>>
>>
>> "Ron Rosenfeld" <[email protected]>
>> ???????:[email protected]...
>>> On Sat, 24 Dec 2005 07:54:52 -0500, Ron Rosenfeld
>>> <[email protected]>
>>> wrote:
>>>
>>>>On Sat, 24 Dec 2005 19:24:06 +0800, "Andy Chan"
>>>><[email protected]> wrote:
>>>>
>>>>>Dear all,
>>>>>
>>>>> I have named a range "Scores" across 3 sheets (i.e.
>>>>>Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in
>>>>>"Scores"
>>>>>in Sheet1 ONLY? I don't know how to provide the target range in the
>>>>>function
>>>>>MEDIAN().
>>>>>
>>>>>Best Regards,
>>>>>Andy Chan
>>>>>
>>>>
>>>>
>>>>=MEDIAN(Sheet3:Sheet1!A1:B10)
>>>>
>>>>
>>>>--ron
>>>
>>> I neglected to mention that you can define scores as that cell
>>> reference:
>>>
>>> scores =Sheet3:Sheet1!$A$1:$B$10
>>>
>>>
>>> --ron
>>
>>
>
>

Dear Ron,

Yes! That's what I want! Thanks!

Best Regards,
Andy

"Ron Rosenfeld" <[email protected]>
???????:[email protected]...
> On Sat, 24 Dec 2005 23:06:05 +0800, "Andy Chan"
> <[email protected]> wrote:
>
>>I only want to compute the median of the data in Sheet1 only... But I want
>>to use the name of the range (Scores)... How can I do it?
>>
>>
>
> If I understand you correctly, you wish to use the name Scores on each of
> several sheets, and have it refer the that sheet only.
>
> One way to do this is to enter three names:
>
> Navigate to Sheet1 and then define a name:
>
> Instead of NAMEing as Scores, use the NAME Sheet1!Scores and set it equal
> to
> A1:B10.
>
> Then navigate to Sheet2 and define a name as Sheet2!Scores referring to
> Sheet2!A1:B10.
>
> The same for Sheet3.
>
> If you are on the 'native' sheet, you can refer to just Scores -- e.g
> =MEDIAN(Scores).
>
> If you are on, let us say, Sheet1 and you want to refer to sheet3, you
> could
> use the formula: =MEDIAN(Sheet3!Scores)
>
>
> --ron

On Sun, 25 Dec 2005 02:40:23 +0800, "Andy Chan"
<[email protected]> wrote:

>Dear Ron,
>
> Yes! That's what I want! Thanks!
>
>Best Regards,
>Andy

Glad to help. I'm also glad I finally understood the nature of your problem;
sorry it took me so long.


--ron