Multiple validation lists

**loupylou** · 03-10-2010, 04:25 PM

I am attaching a small sample of the file I am using. My file is actually almost 10,000 rows in the WarehousDetailRpt tab which is one of about 5 more I need to capture and run this way.

My questions are as below:

Is it possible to see everything in a selected "type" or the overall project?
Is there a less complicated way to group the second validation list and "tie" it back to the first validation list?

I do not know VB at all as simple one I record works only on a good day so please if that is required be extremely detailed.

**DonkeyOte** · 03-11-2010, 07:05 AM

First off ... in your real file we are to assume I think that you have more than one Description per Type ?
(else why the need for the dependent validation in the first instance?)

If as assumed you do have 1:many relationship between Type & Description then the next question would be:

Are you going to be returning multiple type/projects simultaneously on Report tab or only ever one at a time ?

If you're only ever running one project at any given time you can just generate the listing for the one chosen project rather than generating a list for each.
It follows that the single list would mean the DV source would be constant - ie no need for INDIRECT

If the above is not the case then post back - preferably with as much detail as you can - for ex. is the Invty sheet sourced from the Warehouse sheet and if so, how ?

**loupylou** · 03-11-2010, 10:00 AM

Here is the intent of the file. The Report is the only tab that the End User sees. They will be only selecting from the drop down box in column A. Once they choose something in column A, they are given a limited list in column B. Everything is generated from these two selections.

The information for the selections in A and B drop down list are located on tab Validation Info. The reason I wanted to know about a less complicated way of "tying" the two validation list together is that I may at times have one type that can have ten descriptions and in addition one project can have 100+ types. That is quite a bit of name defining for the validation list.

The WarehouseDetailsRpt is where column C on down gathers its information. Cell C2 is getting its information from tab Invty.

So would it be possible to see everything in a selected "type" or the overall project? And is there a less complicated way to group the second validation list and "tie" it back to the first validation list?

I hope this answers some of the questions. The report I attached as a sample is a good representative of what the actual project is like. I just wanted to make the forum aware that the actual I currently am looking at is somewhat large and has the possibility of utilizing an entire tab. Sorry for any confusion.

**loupylou** · 03-12-2010, 08:58 AM

Bump no response

**DonkeyOte** · 03-12-2010, 12:31 PM

To be honest there's still a lot I for one don't understand fully ...

1 - there are seemingly formulae in your real file that are not in this (Invty)
(this makes it difficult to determine how all the component parts fit together - remember no-one else here is at all familiar with your file - what may seem obvious to you will not be to others)

2 - no Type in your file has more than 1 description and this is the fundamental issue
(this makes it virtually impossible to test / visualise the issues you're facing)

3 - you mention "projects with 100 types" implying this to be an important factor yet I see no references to Projects anywhere in your file.

4 - you didn't answer my question regards whether or not you're conducting only one type at any given time ?
(in your sample this is the implication given B2 is the only cell with dependent validation)

5 - it's not clear to me if Validation Info sheet is in any way sourced from Warehouse sheet or is created manually ?
If the lists are created manually I'm not quite sure what you're looking to do exactly.
If the description listings for each type are to be automatically generated based on other data within the file then the questions would be:
a) from where ?
b) using what formulae ? (presently)

Without seeing the full picture in terms of existing workflow it's hard for anyone to offer any great insights I'm afraid.

The above may seem like a rant - it isn't - in short though I have little doubt you can streamline what you're doing given the above points it's hard for anyone offer anything truly meaningful.

We need to understand the workflow and test on a meaningful sample.

**loupylou** · 03-16-2010, 09:32 AM

I guess the best way for me to do this is to break it down into sections. I will start will my first issues on the multiple validation list. The tab that is mark Validation Info is where the drop down boxes are getting there information from. Then post later with the calculations that are linked to the drop down boxes.

There are two questions I am trying to answer:

First, how can I do an ALL selection for column A in the Report tab which in turn would give me an ALL result in column B on tab Report?

Secondly, is there an easier was to name define the validation list?

I am attaching a "sample" workbook that is 2003 excel.

**DonkeyOte** · 03-16-2010, 01:42 PM

This thread isn't really getting anywhere as we now have less data than before.

My final contribution in response to your last post would be that given the data on Validation info sheet is seemingly being created manually you should look to kill two birds with one stone:

a) store the item & type in a table

b) generate a unique type list based on manual table

c) generate a unique item list based on combination of user selection (report Col A) and table info.

Hopefully it might help you with your ongoing efforts. Good luck.