Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with formula (2003)

    Hello Everyone

    Please can you help me with a formula problem I have got.

    I will try my best to explain what it is I'm trying to achieve but please shout if it is still unclear. (I have attached a sample of what I'm trying to do)

    Basically the pivot table data is set and I can't add to it, but in order to add extra analysis I have added some columns to show how many and what type of vehicles were sold on each invoice. My problem is that when I use the VLOOKUP function it can't find more than one type of vehicle linked to the Number. In the yellow highlighted lines you will see that there have been two different types of vehicles sold but I can't get my formula's to reflect this.

    Kind regards
    Hayden

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with formula (2003)

    See if the functions in <post#=395235>post 395235</post#> can be adapted.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with formula (2003)

    Hello Steve

    Thanks for the help but I'm very confused.

    I managed to get the part of copying the code to a new module in Personal.xls, but after that things are a mystery:

    How do I get the code to work? (I don't even know which function to use?)
    Where do I enter the formulas?

    My apologies for my complete and utter ineptitude.

    Kind regards
    Hayden

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with formula (2003)

    In N2 enter:
    =(IF(ISERROR(PERSONAL.XLS!VLindex(E2,VehiclesSold, 2,2)),"",PERSONAL.XLS!VLindex(E2,VehiclesSold,2,2) ))

    In O2 enter:
    =(IF(ISERROR(PERSONAL.XLS!VLindex(E2,VehiclesSold, 1,2)),"",PERSONAL.XLS!VLindex(E2,VehiclesSold,1,2) ))

    Then autofill them down the rows

    In Col N you are choosing the 2nd column to the right the 2nd instance and in Col O, the first col to the right of the lookup, the 2nd instance). See the original post for more details on the functions.

    Note: Functions in other workbooks need to have the sheetname added to them (you can add this functions to this sheet, if they will only be used here and then eliminate the filename, or add them to an addin file)

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with formula (2003)

    Wow Steve!!!

    I'm sitting here shaking my head in utter disbelief. Also the realisation of just how far behind, my excel skills are, is very frightening indeed.

    Thank you very very much its working briliantly!!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with formula (2003)

    Glad I could help.

    Steve

Posting Permissions

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