I have a large spreadsheet (20,000+ rows) with company names (column A),
employee names (column B), and whether each employee is active ("1"=active,
"0"=inactive - column C). This spreadsheet is sorted by company name, then
by employee name.

On a separate sheet I have a drop-down where you select a company. That
selection populates a second drop-down with ALL the employees of that company
(getting the list from the first spreadsheet mentioned above). I am having a
difficult time coming up with an array formula that will allow me to populate
the second drop-down with only the ACTIVE employees of the selected client.
Can anyone help with this?

Thanks.

An easy way to create this would be to create a PivotTable.
Selecting your raw data on Sheet1, go to Data, PivotTable. Click 'next'
(default choices), then make sure raw data is still selected and hit "finish".

You should now see a blank Pivot Table. Drag the Company name field into the
top "page field" area. Then, drag the Status/Active field also into the page
field area, just below Company name. Finally, drag employee name into the Row
field area.

You can now use the page field dropdowns to quickly select company/status,
and see all the employees.

Nice name, btw. ;-)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke" wrote:

I have a large spreadsheet (20,000+ rows) with company names (column A),
employee names (column B), and whether each employee is active ("1"=active,
"0"=inactive - column C). This spreadsheet is sorted by company name, then
by employee name.

On a separate sheet I have a drop-down where you select a company. That
selection populates a second drop-down with ALL the employees of that company
(getting the list from the first spreadsheet mentioned above). I am having a
difficult time coming up with an array formula that will allow me to populate
the second drop-down with only the ACTIVE employees of the selected client.
Can anyone help with this?

Thanks.

I appreciate the PivotTable suggestion. I may be able to make use of that in
a different spreadsheet. But in this case I really need to come up with a
formula (array or otherwise) that can generate a small array from a larger
one. Generating the large array isn't very difficult, it's getting rid of
all the data I don't need that's presenting the problem.

And the name is pretty cool. I really would have done a double-take if the
last initial had been the same, tool.

"Luke M" wrote:

An easy way to create this would be to create a PivotTable.
Selecting your raw data on Sheet1, go to Data, PivotTable. Click 'next'
(default choices), then make sure raw data is still selected and hit "finish".

You should now see a blank Pivot Table. Drag the Company name field into the
top "page field" area. Then, drag the Status/Active field also into the page
field area, just below Company name. Finally, drag employee name into the Row
field area.

You can now use the page field dropdowns to quickly select company/status,
and see all the employees.

Nice name, btw. ;-)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke" wrote:

I have a large spreadsheet (20,000+ rows) with company names (column A),
employee names (column B), and whether each employee is active ("1"=active,
"0"=inactive - column C). This spreadsheet is sorted by company name, then
by employee name.

On a separate sheet I have a drop-down where you select a company. That
selection populates a second drop-down with ALL the employees of that company
(getting the list from the first spreadsheet mentioned above). I am having a
difficult time coming up with an array formula that will allow me to populate
the second drop-down with only the ACTIVE employees of the selected client.
Can anyone help with this?

Thanks.

Hi Luke,

If you email me off-list I have a function in Beta-test that sounds like it
does what you are looking for.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Luke" wrote in message
...
I appreciate the PivotTable suggestion. I may be able to make use of that
in
a different spreadsheet. But in this case I really need to come up with a
formula (array or otherwise) that can generate a small array from a larger
one. Generating the large array isn't very difficult, it's getting rid of
all the data I don't need that's presenting the problem.

And the name is pretty cool. I really would have done a double-take if
the
last initial had been the same, tool.

"Luke M" wrote:

An easy way to create this would be to create a PivotTable.
Selecting your raw data on Sheet1, go to Data, PivotTable. Click 'next'
(default choices), then make sure raw data is still selected and hit
"finish".

You should now see a blank Pivot Table. Drag the Company name field into
the
top "page field" area. Then, drag the Status/Active field also into the
page
field area, just below Company name. Finally, drag employee name into the
Row
field area.

You can now use the page field dropdowns to quickly select
company/status,
and see all the employees.

Nice name, btw. ;-)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke" wrote:

I have a large spreadsheet (20,000+ rows) with company names (column
A),
employee names (column B), and whether each employee is active
("1"=active,
"0"=inactive - column C). This spreadsheet is sorted by company name,
then
by employee name.

On a separate sheet I have a drop-down where you select a company.
That
selection populates a second drop-down with ALL the employees of that
company
(getting the list from the first spreadsheet mentioned above). I am
having a
difficult time coming up with an array formula that will allow me to
populate
the second drop-down with only the ACTIVE employees of the selected
client.
Can anyone help with this?

Thanks.