Excel 2008 : Create list using defined criteria

[KatBoo]

Hi all, I have been trying for a few days to do the following (I've searched the web and this forum extensively with no luck).

I have a workbook that is used to create new job numbers. To do this a PM must first enter the client's name, then the brand name (e.g. client = Joe Smith, brand = Joe's cafe).

I have two sets of lists on two worksheets.

Sheet1 - Column A has a list of every client, column B sets the status of each client (i.e. active or closed).

Sheet2 - Column A has a list of brands, column B has which client is that brand is associated with. Column B is also populated by list validation (which calls the defined list name 'client_list' from Sheet1.

On the third sheet I want PMs to be able to select:
A1 - drop down of 'active' clients
B2 - drop down of brands associated with that client

I'm using 2008 so I believe that VBA isn't an option here and I'm not very familiar with AppleScript, so ideally I'd like to be able to do this using only formulas / rules etc. I've also tried using pivot tables to create a filtered list to base my defined named list on, however both the client and brand list grow over time and new records aren't counted (unless I set the list cell range to be super long, but then the drop down is full of blanks and CPU dies).

Any advise / tips / help would be incredibly welcome!! Thanks guys!

[DonkeyOte]

You can automate this with formulae but you will need to create separate physical listings (obviously)

A sample file would be helpful - for ex.: How is client_list defined - does client_list include Column B ?

The most efficient solution re: active clients would be to use an adjacent numerical key thereby permitted a basic INDEX/MATCH approach and negating need for Arrays.

As for subsequent Product Listing - are you looking to run multiple clients simultaneously or would a PM only ever be selecting a single Client ?