a single facility # be selected from a drop-down list and in return an array of cells

**dl00051** · 02-21-2008, 05:54 PM

I have a database of vehicle information (3000 rows by 20 columns) for cars that are at several different facilities. What I am trying to do is have a single facility # be selected from a drop-down list and in return an array of cells will be filled in with the information from the database that corresponds to that facility # only (approx. 30 rows by 20 columns).

I have the feeling that I will have to use VBA to get what I need, but have no idea where to start on finding the coding that is going to work.

Any help is greatly appreciated!

**oldchippy** · 02-21-2008, 06:01 PM

Hi,

Welcome to the forum, take a look at this link on filtering

http://www.contextures.com/xladvfilter01.html

**stevekirk** · 02-21-2008, 06:03 PM

hi dl00051,

welcome to the forum

i would use a pivot table to get the results you need

steve

**dl00051** · 02-22-2008, 04:10 PM

Thanks for the welcome and the advice. I seem to be a step closer, I just hope it's in the right direction. Here's the situation....

Using the advanced filter I can pull one facility's list, but I have a drop-down list to choose the facility if I want to select a second and it will not filter in the corresponding vehicles once I change the facility #.

A PivotTable won't suffice because I am looking to send this sheet out to the field and have them each pull up their respective facility and be able to add in other vehicles that they have on site.

Sorry if this was clear as mud, but any advice is welcomed!

**oldchippy** · 02-22-2008, 05:26 PM

Hello there,

It would be better if you could provide a small zipped sample of your worksheet and also the expected result; it would be easier to follow.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**dl00051** · 02-25-2008, 11:35 AM

File is attached. Thanks for the help!

This is just a small basic sample of what I'm looking for.
What I need is when a Facility # is selected from the list, it will fill in the corresponding vehicles that are located at that facility according to the Database.

Thanks again!

**oldchippy** · 02-25-2008, 05:41 PM

Hi,

Have you considered filtering the database itself? Go to Data > Filter > Autofilter, then filter by Facility.

Or may be this - Extract the data to another sheet

http://www.contextures.com/xladvfilter01.html#ExtractWs

**dl00051** · 02-27-2008, 03:53 PM

The advanced filter option would most likely take care of my issue, but it only seems to work with a criteria that is already established in a cell(s). I need it to be a blank criteria that can be filled it with a selected facility #.

I am planning on sending this file out to each facility and want them to be able to select there facility number from the drop-down list so their current inventory will be pulled. They can then add any other vehicles on site, and then return the file back to me for updates.

Maybe I'm looking at this all wrong, but it originally made sense in my mind. I greatly appreciate the help though!

Thanks