Index and row problem ( tricky ) :(

[vddavid]

HI everybody ,


I am having a hard ( extremely ) time to solve the following problem , can somebody help me out ?
Consider a sheet divided in two different parts , the datas and the resume .
The datas : numbers Town Name Year
1001 A test1 97
2004 B test1 98
1785 C test2 99
3652 D test4 00
4587 E test6 01
1000 F test7 02
5624 G test3 98
1001 A test4 99

And the resume : three drop down boxes ;
Names ( test1 > test 10 )
Year ( 97 > 02 )
The State ( Alabama > Texas )

I would like to select all datas that complete the chosen criteria in the drop down boxes . Knowing that if the number is

between 1000 2000 > Minesota
2000 3000 > alabama
3000 4000 > jersey
4000 5000 california
5000 6000 florida
6000 7000 texas


Any ideas

Many thanks

David van Dea

[Dave Hawley]

Hi David

Not a definitive answer, but I have some examples here that may be of interest to you.

http://www.ozgrid.com/download/default.htm
Under: DFunctionsWithValidation.zip

[vddavid]

Thanks ,

Indeed this site can be very helpful , but unfortunately it doesn't answer my question.
Any other ideas .

david

[Aladin Akyurek]

On 2002-03-19 06:56, vddavid wrote:
HI everybody ,


I am having a hard ( extremely ) time to solve the following problem , can somebody help me out ?
Consider a sheet divided in two different parts , the datas and the resume .
The datas : numbers Town Name Year
1001 A test1 97
2004 B test1 98
1785 C test2 99
3652 D test4 00
4587 E test6 01
1000 F test7 02
5624 G test3 98
1001 A test4 99

And the resume : three drop down boxes ;
Names ( test1 > test 10 )
Year ( 97 > 02 )
The State ( Alabama > Texas )

I would like to select all datas that complete the chosen criteria in the drop down boxes . Knowing that if the number is

between 1000 2000 > Minesota
2000 3000 > alabama
3000 4000 > jersey
4000 5000 california
5000 6000 florida
6000 7000 texas


Any ideas

Many thanks

David van Dea

Hi David,

After a long deliberation, I had to conclude that your problem can be best attacked with the Pivot Tables.

I created an Admin sheet which now houses all your lookup tables. I added an additional table, directly related to the issue you mention above: Knowing that if the number is between 1000 and 2000 > Minesota.

The table looks like this:

{1000,"Bruxelles";
2000,"Charleroi";
3000,"Mons"}

where Bruxelles is a so-called Secteur which consists of Localités numbered 1000 thru 2000 exclusive.

I've also moved your data to a worksheet of its own: Données. The data area, A1:K1, has the following column hedings/labels:

{"Ref","Bloc","Stat","Chassis","Marque","Année","Nom","CP","Localité","Secteur","Date"}

Date has the values that you originally had under Année.

The following formula in F from F2 on makes up the values of Année:

=YEAR(K2)

The following formula in J from J2 on makes up the values of Secteur:

=VLOOKUP(H2,{1000,"Bruxelles";2000,"Charleroi";3000,"Mons"},2)

Note that this formula incorporates directly the additional table I mentioned above.

I constructed a Pivot Table, where

PAGE consists of the fields (column headings):

Marque
Secteur
Année

ROW

Localité

DATA

Localité (count of)

One way Pivot Table looks like is:

{"Marque","Audi";
"Secteur","Bruxelles";
"Année",1997;"","";
"Count of Localité","";
"Localité","Total";
"Havre",1;
"Grand Total",1}

I believe the above approximates nicely the queries that you wanted to run on your data.

Aladin

The WB is underway to you.



[ This Message was edited by: Aladin Akyurek on 2002-03-31 16:06 ]

[ This Message was edited by: Aladin Akyurek on 2002-03-31 16:07 ]