I have already have my lists created.... Problem... in Cells A17:A62 I need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far I have
Cell G10 as a drop down list to select the crew name, which puts the crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that make
sense?? PLEASE NEED HELP

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62 I
need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far I
have
Cell G10 as a drop down list to select the crew name, which puts the crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that make
sense?? PLEASE NEED HELP

yes I do have a table with that info.. and no on the same size... I will have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62 I
need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far I
have
Cell G10 as a drop down list to select the crew name, which puts the crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that make
sense?? PLEASE NEED HELP

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62 I
need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that
make
sense?? PLEASE NEED HELP

that works perfectly for what I am doing... with a very few adjustments my
form works great... thanks for your help on this matter...

"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62 I
need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that
make
sense?? PLEASE NEED HELP

Opps... Biff I thought it was going to work.... but after the second line it
gives me a #NUM error and it is not pulling up the right name with the four
digit crew number... It brings up a name but from someone elses crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62 I
need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that
make
sense?? PLEASE NEED HELP

Biff,
I downloaded you file in the hope I could improve my Excel
knowledge but I haven't quite grasped how your formula works. I would
appreciate it if you could find time to explain it to me; perhaps post
something on "yousendit"?

TIA

Toppers

"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62 I
need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that
make
sense?? PLEASE NEED HELP

Post the *EXACT* formula that you're using.

Biff

"DarnTootn" wrote in message
...
Opps... Biff I thought it was going to work.... but after the second line
it
gives me a #NUM error and it is not pulling up the right name with the
four
digit crew number... It brings up a name but from someone elses crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62
I
need
the list of employees names to display down the column by selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them.... so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

Here's an explanation I wrote up a while back for someone. The formula works
exactly the same way except that the criteria in this case is different.

=IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A
$8)-ROW(A$2)+1),ROWS($1:1))),"")

The only part of the formula that you actually need is this:

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))

However, if you drag copy down, once the data that meets the criteria is
exhausted the next cell(s) will return #NUM! errors. Errors are unsightly
and can cause problems in any downstream calculations. We can build an error
trap in the formula that catches these errors so that they're not displayed
and won't affect any downstream calculations.

Excel has some error testing functions like : Iserror, Isna, Error.Type.

Using the Iserror function to test for errors and "trap" them, the formula
would look like this:

=IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:
A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

As you can see, this makes the formula about twice as long and, if I was
still using the Sheet references, this would make it even longer! Long
formulas tend to "scare" people! Not only is the formula long but when the
error trap evaluates to FALSE (no error) the formula has to process the data
twice. So naturally, that takes twice as long.

I used a "psuedo" error trap that effectively does the same thing but is
much shorter to express and is more efficient:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac")

=IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))

With the error trap I've used the formula only has to process the data once.

The logic is that you count the number of instances that meet the criteria:

COUNTIF(B$2:B$8,"vac")

Then compare that to the number of cells that the formula is being copied
to:

ROWS($1:1)

When you drag copy down to more cells the ROWS($1:1) function will increment
to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on
your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this:

=IF(1<=2,value_if_true,value_if_false)
=IF(2<=2,value_if_true,value_if_false)
=IF(3<=2,value_if_true,value_if_false)
etc

The value_if_true argument is:

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

The value_if_false argument is: ""

Returns a blank cell instead of an error, #NUM!

Now, let's see what's happening when the value_if_true argument is met.

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates.

There are a total of 7 elements in the range A$2:A$8. The Index function
holds these elements in a relative order. That order is the total number of
elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe

A2 = 1
A3 = 2
A4 = 3
...
A8 = 7

Now we need to tell the formula which elements of that range to return based
on meeting the criteria. That criteria is:

IF(B$2:B$8="vac"

This will return an array of TRUE's or FALSE's. Based on the posted example
that would be:

FALSE
FALSE
TRUE
FALSE
FALSE
TRUE
FALSE


Ok, the value_if_true argument is:

ROW(A$2:A$8)-ROW(A$2)+1

And the value_if_false argument is nothing. No value_if_false argument was
defined and when that happens the default return is FALSE. We'll see how
that comes into play later on.

Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1

Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7),
we need a means of generating an array of numbers from 1 to 7 that
correspond to the indexed elements. That's where ROW comes in handy.

ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8
and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To
take care of that we subtract the offset then add 1: -ROW(A$2)+1

This is how that is processed in the formula:

2 - 2 +1 = 1
3 - 2 + 1 =2
4 - 2 + 1 =3
5 - 2 + 1 =4
...
8 - 2 + 1 =7

Now we have our array from 1 to 7 that correspond to the indexed array of 1
to 7.

There are other ways to generate that array but this is the most
"foolproof".

So, now we put this all together to generate yet another array:

If TRUE = ROW number, if FALSE = FALSE:

B2 = vac = FALSE = FALSE
B3 = vac = FALSE = FALSE
B4 = vac = TRUE = 3
B5 = vac = FALSE = FALSE
B6 = vac = FALSE = FALSE
B7 = vac = TRUE = 6
B8 = vac = FALSE = FALSE

That array is then passed to the SMALL function:

SMALL({F,F,3,F,F,6,F},ROWS($1:1))

As is, that evaluates to the first smallest value which is 3. When drag
copied down the ROWS function will increment to $1:2 for the second
smallest, $1:3 for the third smallest, etc. Since there is no third smallest
that would generate a #NUM! error but remember, we have that taken care of
using our "psuedo" error trap.

Putting it all together. When copied down this is what you get:

INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4
INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7
INDEX(A$2:A$8,#NUM!) = "" (blank)

There you have it!

Biff

"Toppers" wrote in message
...
Biff,
I downloaded you file in the hope I could improve my Excel
knowledge but I haven't quite grasped how your formula works. I would
appreciate it if you could find time to explain it to me; perhaps post
something on "yousendit"?

TIA

Toppers

"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62
I
need
the list of employees names to display down the column by selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them.... so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

I posted here

javascript:ol('http://download.yousendit.com/A3DC28565957C69A');
"Biff" wrote:

Post the *EXACT* formula that you're using.

Biff

"DarnTootn" wrote in message
...
Opps... Biff I thought it was going to work.... but after the second line
it
gives me a #NUM error and it is not pulling up the right name with the
four
digit crew number... It brings up a name but from someone elses crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62
I
need
the list of employees names to display down the column by selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them.... so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

Biff,
Thanks for the explanation ... which I haven't yet read! I'll
need a clear head to get my mind round it and then play a little. With me
it's age ... the grey matter is declining fast.

Thanks again.


"Biff" wrote:

Here's an explanation I wrote up a while back for someone. The formula works
exactly the same way except that the criteria in this case is different.

=IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A $8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A
$8)-ROW(A$2)+1),ROWS($1:1))),"")

The only part of the formula that you actually need is this:

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))

However, if you drag copy down, once the data that meets the criteria is
exhausted the next cell(s) will return #NUM! errors. Errors are unsightly
and can cause problems in any downstream calculations. We can build an error
trap in the formula that catches these errors so that they're not displayed
and won't affect any downstream calculations.

Excel has some error testing functions like : Iserror, Isna, Error.Type.

Using the Iserror function to test for errors and "trap" them, the formula
would look like this:

=IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:
A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

As you can see, this makes the formula about twice as long and, if I was
still using the Sheet references, this would make it even longer! Long
formulas tend to "scare" people! Not only is the formula long but when the
error trap evaluates to FALSE (no error) the formula has to process the data
twice. So naturally, that takes twice as long.

I used a "psuedo" error trap that effectively does the same thing but is
much shorter to express and is more efficient:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac")

=IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))

With the error trap I've used the formula only has to process the data once.

The logic is that you count the number of instances that meet the criteria:

COUNTIF(B$2:B$8,"vac")

Then compare that to the number of cells that the formula is being copied
to:

ROWS($1:1)

When you drag copy down to more cells the ROWS($1:1) function will increment
to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on
your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this:

=IF(1<=2,value_if_true,value_if_false)
=IF(2<=2,value_if_true,value_if_false)
=IF(3<=2,value_if_true,value_if_false)
etc

The value_if_true argument is:

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

The value_if_false argument is: ""

Returns a blank cell instead of an error, #NUM!

Now, let's see what's happening when the value_if_true argument is met.

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates.

There are a total of 7 elements in the range A$2:A$8. The Index function
holds these elements in a relative order. That order is the total number of
elements. There are 7 elements so the order is 1,2,3,4,5,6,7 whe

A2 = 1
A3 = 2
A4 = 3
...
A8 = 7

Now we need to tell the formula which elements of that range to return based
on meeting the criteria. That criteria is:

IF(B$2:B$8="vac"

This will return an array of TRUE's or FALSE's. Based on the posted example
that would be:

FALSE
FALSE
TRUE
FALSE
FALSE
TRUE
FALSE


Ok, the value_if_true argument is:

ROW(A$2:A$8)-ROW(A$2)+1

And the value_if_false argument is nothing. No value_if_false argument was
defined and when that happens the default return is FALSE. We'll see how
that comes into play later on.

Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1

Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7),
we need a means of generating an array of numbers from 1 to 7 that
correspond to the indexed elements. That's where ROW comes in handy.

ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8
and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To
take care of that we subtract the offset then add 1: -ROW(A$2)+1

This is how that is processed in the formula:

2 - 2 +1 = 1
3 - 2 + 1 =2
4 - 2 + 1 =3
5 - 2 + 1 =4
...
8 - 2 + 1 =7

Now we have our array from 1 to 7 that correspond to the indexed array of 1
to 7.

There are other ways to generate that array but this is the most
"foolproof".

So, now we put this all together to generate yet another array:

If TRUE = ROW number, if FALSE = FALSE:

B2 = vac = FALSE = FALSE
B3 = vac = FALSE = FALSE
B4 = vac = TRUE = 3
B5 = vac = FALSE = FALSE
B6 = vac = FALSE = FALSE
B7 = vac = TRUE = 6
B8 = vac = FALSE = FALSE

That array is then passed to the SMALL function:

SMALL({F,F,3,F,F,6,F},ROWS($1:1))

As is, that evaluates to the first smallest value which is 3. When drag
copied down the ROWS function will increment to $1:2 for the second
smallest, $1:3 for the third smallest, etc. Since there is no third smallest
that would generate a #NUM! error but remember, we have that taken care of
using our "psuedo" error trap.

Putting it all together. When copied down this is what you get:

INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4
INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7
INDEX(A$2:A$8,#NUM!) = "" (blank)

There you have it!

Biff

"Toppers" wrote in message
...
Biff,
I downloaded you file in the hope I could improve my Excel
knowledge but I haven't quite grasped how your formula works. I would
appreciate it if you could find time to explain it to me; perhaps post
something on "yousendit"?

TIA

Toppers

"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things as
easy to understand as possible I left some sheet references in that could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells A17:A62
I
need
the list of employees names to display down the column by selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them.... so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts the
crew
four digit number in A13. So now I need Cells A17:A62 to display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

Argh! Merged cells! Argh!

Let me see what I can do. It may be a while. I'm getting ready for a dinner
break.

Biff

"DarnTootn" wrote in message
...
I posted here

javascript:ol('http://download.yousendit.com/A3DC28565957C69A');
"Biff" wrote:

Post the *EXACT* formula that you're using.

Biff

"DarnTootn" wrote in message
...
Opps... Biff I thought it was going to work.... but after the second
line
it
gives me a #NUM error and it is not pulling up the right name with the
four
digit crew number... It brings up a name but from someone elses crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows
to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things
as
easy to understand as possible I left some sheet references in that
could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective
crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells
A17:A62
I
need
the list of employees names to display down the column by
selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them....
so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts
the
crew
four digit number in A13. So now I need Cells A17:A62 to display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

Merged cells are nothing but *TROUBLE* !!!!!!!!!!!!!!!!!

This should have been a very simple thing to do but the merged cells
required a different approach. I had to use a helper column on the Employees
sheet, column J.

I changed the formulas for the hourly rates to something less "complex" (and
much shorter!):

=IF(A17="",0,VLOOKUP(A17,Employees!A$3:E$78,4,0))

Here's your file:

http://download.yousendit.com/C912583833031EA3

Remember, merged cells are a cancer!

Biff

"Biff" wrote in message
...
Argh! Merged cells! Argh!

Let me see what I can do. It may be a while. I'm getting ready for a
dinner break.

Biff

"DarnTootn" wrote in message
...
I posted here

javascript:ol('http://download.yousendit.com/A3DC28565957C69A');
"Biff" wrote:

Post the *EXACT* formula that you're using.

Biff

"DarnTootn" wrote in message
...
Opps... Biff I thought it was going to work.... but after the second
line
it
gives me a #NUM error and it is not pulling up the right name with the
four
digit crew number... It brings up a name but from someone elses crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows
to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things
as
easy to understand as possible I left some sheet references in that
could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective
crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells
A17:A62
I
need
the list of employees names to display down the column by
selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them....
so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts
the
crew
four digit number in A13. So now I need Cells A17:A62 to
display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

thank you biff... I got it working... I had to make a couple of adjustments
with the code cuz I need it to run down 150 instead of just the 78 that were
shown... those were old employees that were termiated ,,, anyway thank you so
so much

"Biff" wrote:

Merged cells are nothing but *TROUBLE* !!!!!!!!!!!!!!!!!

This should have been a very simple thing to do but the merged cells
required a different approach. I had to use a helper column on the Employees
sheet, column J.

I changed the formulas for the hourly rates to something less "complex" (and
much shorter!):

=IF(A17="",0,VLOOKUP(A17,Employees!A$3:E$78,4,0))

Here's your file:

http://download.yousendit.com/C912583833031EA3

Remember, merged cells are a cancer!

Biff

"Biff" wrote in message
...
Argh! Merged cells! Argh!

Let me see what I can do. It may be a while. I'm getting ready for a
dinner break.

Biff

"DarnTootn" wrote in message
...
I posted here

javascript:ol('http://download.yousendit.com/A3DC28565957C69A');
"Biff" wrote:

Post the *EXACT* formula that you're using.

Biff

"DarnTootn" wrote in message
...
Opps... Biff I thought it was going to work.... but after the second
line
it
gives me a #NUM error and it is not pulling up the right name with the
four
digit crew number... It brings up a name but from someone elses crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23 rows
to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make things
as
easy to understand as possible I left some sheet references in that
could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their respective
crew
number?

Biff

"DarnTootn" wrote in message
...
I have already have my lists created.... Problem... in Cells
A17:A62
I
need
the list of employees names to display down the column by
selecting
the
number value in Cell A13...(A13 being a four digit crew code)...
Each
employee has a four digit crew number associated with them....
so
far
I
have
Cell G10 as a drop down list to select the crew name, which puts
the
crew
four digit number in A13. So now I need Cells A17:A62 to
display
the
employee names down the column by using the value in A13... does
that
make
sense?? PLEASE NEED HELP

You're welcome!

Biff

"DarnTootn" wrote in message
...
thank you biff... I got it working... I had to make a couple of
adjustments
with the code cuz I need it to run down 150 instead of just the 78 that
were
shown... those were old employees that were termiated ,,, anyway thank you
so
so much

"Biff" wrote:

Merged cells are nothing but *TROUBLE* !!!!!!!!!!!!!!!!!

This should have been a very simple thing to do but the merged cells
required a different approach. I had to use a helper column on the
Employees
sheet, column J.

I changed the formulas for the hourly rates to something less "complex"
(and
much shorter!):

=IF(A17="",0,VLOOKUP(A17,Employees!A$3:E$78,4,0))

Here's your file:

http://download.yousendit.com/C912583833031EA3

Remember, merged cells are a cancer!

Biff

"Biff" wrote in message
...
Argh! Merged cells! Argh!

Let me see what I can do. It may be a while. I'm getting ready for a
dinner break.

Biff

"DarnTootn" wrote in message
...
I posted here

javascript:ol('http://download.yousendit.com/A3DC28565957C69A');
"Biff" wrote:

Post the *EXACT* formula that you're using.

Biff

"DarnTootn" wrote in message
...
Opps... Biff I thought it was going to work.... but after the
second
line
it
gives me a #NUM error and it is not pulling up the right name with
the
four
digit crew number... It brings up a name but from someone elses
crew


"Biff" wrote:

Here's a small sample file that demonstrates this:

List_names.xls 15kb

http://s65.yousendit.com/d.aspx?id=3...W0G3N0SXXWLA2T

Sheet2 has the employee list.

For your situation you'd need to copy the formula in A17 down 23
rows
to
cover the max number of employees in any one crew.

The formula can be shortened slightly but in an effort to make
things
as
easy to understand as possible I left some sheet references in
that
could
be
removed.

Biff

"DarnTootn" wrote in message
...
yes I do have a table with that info.. and no on the same
size... I
will
have
anywhere from 3-23

"Biff" wrote:

Hi!

Does each crew have the same number of people assigned?

Do you have a table that lists the employees and their
respective
crew
number?

Biff

"DarnTootn" wrote in
message
...
I have already have my lists created.... Problem... in Cells
A17:A62
I
need
the list of employees names to display down the column by
selecting
the
number value in Cell A13...(A13 being a four digit crew
code)...
Each
employee has a four digit crew number associated with
them....
so
far
I
have
Cell G10 as a drop down list to select the crew name, which
puts
the
crew
four digit number in A13. So now I need Cells A17:A62 to
display
the
employee names down the column by using the value in A13...
does
that
make
sense?? PLEASE NEED HELP