Informational Search
 

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Why don't you just sort sheet1 based on the time?

If you don't want to or can't do that then how about just making a copy of
sheet1 and then sorting on the copied sheet.

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place
number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Number Search
 

I'm going to change the senario,

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the place
they are from, that is next to that number on sheet one will apear.

Eventually I will come back to putting them in numerical order. Also, I
don't want to have to copy the sheet and use the "Data Sort" button.


"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Number Search
 

Assume Sheet2 A1 is where you enter the number.

Enter this formula in B1 and copy across to C1:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A:$C,COLUMNS($B1 :C1),0))

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
I'm going to change the senario,

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the
place
they are from, that is next to that number on sheet one will apear.

Eventually I will come back to putting them in numerical order. Also, I
don't want to have to copy the sheet and use the "Data Sort" button.


"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place
number.

In Column C, I want it to find the lowest time first, then in the 2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Now for the Second Part.

Sheet one,
Column A: a bunch of numbers in a random order.
Column B: nothing
Column C:

C1: =(min(A1:A50)
Rest of the C Column,
I want it to be the next number to be the next number in numerical order.
i.e. C2 = C1, but only then next largest number.

Hope this makes sence.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Try this:

C1: =SMALL(A$1:A$50,ROW(A1))

then copy that into C2:C10 to get the smallest, next smallest etc, as
the ROW function will return 1, 2, 3 etc as you copy it down.

Hope this helps.

Pete

On Oct 12, 11:31 pm, zoomzoom632
wrote:
Now for the Second Part.

Sheet one,
Column A: a bunch of numbers in a random order.
Column B: nothing
Column C:

C1: =(min(A1:A50)
Rest of the C Column,
I want it to be the next number to be the next number in numerical order.
i.e. C2 = C1, but only then next largest number.

Hope this makes sence.



"zoomzoom632" wrote:
Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.- Hide quoted text -

- Show quoted text -

Informational Search
 

There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So I want
to makesure the correct number matches the correct name, then I'll base the
place they are from off of the name which can only appear once in the list.

Thanks to several responces, I managed to create the page worksheet to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So I want
to makesure the correct number matches the correct name, then I'll base
the
place they are from off of the name which can only appear once in the
list.

Thanks to several responces, I managed to create the page worksheet to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place
number.

In Column C, I want it to find the lowest time first, then in the 2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Yeah, that's basically what I mean, but I forgot to include something in my
previous message. The list is in alphabetical order by the name. so your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So I want
to makesure the correct number matches the correct name, then I'll base
the
place they are from off of the name which can only appear once in the
list.

Thanks to several responces, I managed to create the page worksheet to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place
number.

In Column C, I want it to find the lowest time first, then in the 2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a number
it can be complicated. I assume if you enter the number 1 then you want all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include something in
my
previous message. The list is in alphabetical order by the name. so your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So I
want
to makesure the correct number matches the correct name, then I'll base
the
place they are from off of the name which can only appear once in the
list.

Thanks to several responces, I managed to create the page worksheet to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place
number.

In Column C, I want it to find the lowest time first, then in the 2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Hi Biff,

I thinks he wants one sheet to show the data in name order and another
sheet to contain exactly the same data but sorted in order of time to
finish the race, i.e. sorting by formula rather than manually.

Pete

On Oct 13, 10:34 pm, "T. Valko" wrote:
It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested just
sorting it or making a copy and sorting that copy.

Informational Search
 

Biff,

That's pretty much what I mean, but is there anyway to continue that down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a number
it can be complicated. I assume if you enter the number 1 then you want all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include something in
my
previous message. The list is in alphabetical order by the name. so your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So I
want
to makesure the correct number matches the correct name, then I'll base
the
place they are from off of the name which can only appear once in the
list.

Thanks to several responces, I managed to create the page worksheet to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place
number.

In Column C, I want it to find the lowest time first, then in the 2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

That's pretty much what I mean, but is there anyway to continue that down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested
just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a
number
it can be complicated. I assume if you enter the number 1 then you want
all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include something
in
my
previous message. The list is in alphabetical order by the name. so
your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered,
the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered,
the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So
I
want
to makesure the correct number matches the correct name, then I'll
base
the
place they are from off of the name which can only appear once in
the
list.

Thanks to several responces, I managed to create the page worksheet
to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the
place
number.

In Column C, I want it to find the lowest time first, then in the
2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next
to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Biff,

This is brilliant, thank you very, very much. See I kept track of times for
my high school's track team, I was looking for a way to enter times and what
not using excell and these formulas.

Again thank you very much. Also, what did you mean by, "Array formulas need
to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)"?


"T. Valko" wrote:

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

That's pretty much what I mean, but is there anyway to continue that down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested
just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a
number
it can be complicated. I assume if you enter the number 1 then you want
all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include something
in
my
previous message. The list is in alphabetical order by the name. so
your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered,
the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered,
the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So
I
want
to makesure the correct number matches the correct name, then I'll
base
the
place they are from off of the name which can only appear once in
the
list.

Thanks to several responces, I managed to create the page worksheet
to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the
place
number.

In Column C, I want it to find the lowest time first, then in the
2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next
to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

You're welcome. Thanks for the feedback!

Array formulas are a special kind of formula. When entering a regular
formula you simply type the formula then you hit the ENTER key to enter it
in a cell. Array formulas are different. You type the formula, then, instead
of hitting the ENTER key you *must* use a combination of keys. Those keys
are CTRL, SHIFT and ENTER. That is, hold down both the CTRL key and the
SHIFT key then hit ENTER. When done properly Excel will enclose the formula
in squiggly braces { }. You can not just type these braces in. You *must*
use the key combo to generate them. Also, anytime you edit an array formula
you must re-enter it as an array with the key combo.

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

This is brilliant, thank you very, very much. See I kept track of times
for
my high school's track team, I was looking for a way to enter times and
what
not using excell and these formulas.

Again thank you very much. Also, what did you mean by, "Array formulas
need
to be entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER)"?


"T. Valko" wrote:

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

That's pretty much what I mean, but is there anyway to continue that
down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get
those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're
just
duplicating you data but in a different order. That's why I suggested
just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a
number
it can be complicated. I assume if you enter the number 1 then you
want
all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on
that
selection.

The formulas used to do this are array formulas. Array formulas need
to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include
something
in
my
previous message. The list is in alphabetical order by the name.
so
your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in
message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I
entered,
the
person's name that is next to that number on sheet one will
apear.
Column 3: Based on the number in column 1, sheet 2 that I
entered,
the
place
they are from, that is next to that number on sheet one will
apear.

Now, there's a curve ball, Sheet one has the numbers repeating.
So
I
want
to makesure the correct number matches the correct name, then
I'll
base
the
place they are from off of the name which can only appear once in
the
list.

Thanks to several responces, I managed to create the page
worksheet
to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the
place
number.

In Column C, I want it to find the lowest time first, then in
the
2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed
next
to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Biff,

The equation works weel, but it there anyway to get it to observe more
information, because this is allowing me to look at the top four numbers/time
on the list, I would like to be able to look at 50-60 different numbers, I've
tried various things, but I cant figure out the propper numbers to change.

Thanks.

"T. Valko" wrote:

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

That's pretty much what I mean, but is there anyway to continue that down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested
just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a
number
it can be complicated. I assume if you enter the number 1 then you want
all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include something
in
my
previous message. The list is in alphabetical order by the name. so
your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered,
the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered,
the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So
I
want
to makesure the correct number matches the correct name, then I'll
base
the
place they are from off of the name which can only appear once in
the
list.

Thanks to several responces, I managed to create the page worksheet
to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the
place
number.

In Column C, I want it to find the lowest time first, then in the
2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next
to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

I need to make another change, I did end up figuring out how to get more
date, but when a cell (in the number column) is blank it acts as though it
were zero, it there a way to avoid this? If so, how?

Thanks and sorry.

"zoomzoom632" wrote:

Biff,

The equation works weel, but it there anyway to get it to observe more
information, because this is allowing me to look at the top four numbers/time
on the list, I would like to be able to look at 50-60 different numbers, I've
tried various things, but I cant figure out the propper numbers to change.

Thanks.

"T. Valko" wrote:

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

That's pretty much what I mean, but is there anyway to continue that down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested
just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a
number
it can be complicated. I assume if you enter the number 1 then you want
all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Yeah, that's basically what I mean, but I forgot to include something
in
my
previous message. The list is in alphabetical order by the name. so
your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered,
the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered,
the
place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So
I
want
to makesure the correct number matches the correct name, then I'll
base
the
place they are from off of the name which can only appear once in
the
list.

Thanks to several responces, I managed to create the page worksheet
to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the
place
number.

In Column C, I want it to find the lowest time first, then in the
2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next
to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

Informational Search
 

Based on the most recent sample file, change the formula in F2 and copy down
until you get blanks:

=IF(ROWS(F$2:F2)<=COUNT(A$2:A$5),SMALL(A$2:A$5,ROW S(F$2:F2)),"")

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
I need to make another change, I did end up figuring out how to get more
date, but when a cell (in the number column) is blank it acts as though it
were zero, it there a way to avoid this? If so, how?

Thanks and sorry.

"zoomzoom632" wrote:

Biff,

The equation works weel, but it there anyway to get it to observe more
information, because this is allowing me to look at the top four
numbers/time
on the list, I would like to be able to look at 50-60 different numbers,
I've
tried various things, but I cant figure out the propper numbers to
change.

Thanks.

"T. Valko" wrote:

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an
array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in message
...
Biff,

That's pretty much what I mean, but is there anyway to continue that
down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get
those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

"T. Valko" wrote:

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're
just
duplicating you data but in a different order. That's why I
suggested
just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a
number
it can be complicated. I assume if you enter the number 1 then you
want
all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on
that
selection.

The formulas used to do this are array formulas. Array formulas need
to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER).

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in
message
...
Yeah, that's basically what I mean, but I forgot to include
something
in
my
previous message. The list is in alphabetical order by the name.
so
your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

"T. Valko" wrote:

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

--
Biff
Microsoft Excel MVP


"zoomzoom632" wrote in
message
...
There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I
entered,
the
person's name that is next to that number on sheet one will
apear.
Column 3: Based on the number in column 1, sheet 2 that I
entered,
the
place
they are from, that is next to that number on sheet one will
apear.

Now, there's a curve ball, Sheet one has the numbers repeating.
So
I
want
to makesure the correct number matches the correct name, then
I'll
base
the
place they are from off of the name which can only appear once
in
the
list.

Thanks to several responces, I managed to create the page
worksheet
to
interact like I listed below.

"zoomzoom632" wrote:

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper
infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent
the
place
number.

In Column C, I want it to find the lowest time first, then in
the
2nd
row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed
next
to
the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.