Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Index and row problem ( tricky ) :(

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks ,

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

    david

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,021
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •