Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index/Match ??? (2000 sr1)

    Good Afternoon,
    The software that we use at work will create an Excel Spreadsheet from reports that I run. (Pervasive SQL to Crystal Reports exported to Excel 5.0) but some of the rows and columns get mixed up in the exporting. So let's say I want to look up "ABC123" on a spreadsheet and it turns out that it is located at cell D10, I want to return the value located in cell "F12" (I tried using Index/Match, but because the data is not located in the same row, it doesn't seem to work.) Any help you fine folks can offer would be appreciated.
    Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match ??? (2000 sr1)

    What is the relationship between cell D10 and F12? Is the value you want to return always two cells to the right and two cells down from the cell where you get the match?
    Legare Coleman

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Index/Match ??? (2000 sr1)

    Since the data is not in the same row / column, is there any chance that there are null values in the original data set that are shifting data. Also - which version of Crystal reports are you using? You can also connect directly to Pervasive using Access or Excell using ODBC which may make your export easier. I found that connecting to the Pervasive Engine using ODBC via Access easier then using Crystal reports.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match ??? (2000 sr1)

    Thanks for your reply,
    The cell D10, D14, D18 etc contain a finished product code and F12, F16, F20 etc contain the quantity in stock. When Crystal Reports creates the report it looks fine all the information is on one row but when it is exported to Excel it doesn't end up looking the same. In this example the match I want to find is always two cells down and two to the right, but in other reports the data will always be in a consistent column but the rows will vary.
    Any help you can give on one or the other would be appreciated.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match ??? (2000 sr1)

    Good Morning, I am using Crystal 6.0. I don't believe it's null values causing problems but I could be wrong.
    I have used Excel for a long time and am very comfortable with it and I have never even seen Access in action so I would prefer to work with Excel. If you could point me in a direction to learn more about using the ODBC with Excel I would appreciate it. (I didn't know I could do that!)

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match ??? (2000 sr1)

    I think the formula below will do what you want:

    <pre>=OFFSET(D1,MATCH("ABC123",D1100,0)+2,2)
    </pre>

    Legare Coleman

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Index/Match ??? (2000 sr1)

    I have attached a file that shows how to create the Machine Data Source for MPM (MPM uses Pervasive Brtrieve) so ODBC can be used to connect to it. Once the machine data source is set, excel can connect directly to the data by doing the following steps:

    In Excel,
    Select Data, Get External Data, New Database Query, Select Database Tab, and then the data source.
    If you set up the Machine Data Source correctly, you will see the name of the machine data source here. Excel will connect to the data (which will be flat files) that the machine data source points to. Setting up the ODBC source can be tricky, but once done, it works well.

    We were going to get Crystal Reports but found it easier to connect to the data directly to generate what we needed. Of course, you then need to understand the underlying data tables and relations as well of the data you are connecting to.

    Good luck.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Index/Match ??? (2000 sr1)

    .. Attaching one more time....
    Regards,

    Gary
    (It's been a while!)

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match ??? (2000 sr1)

    Hi Gary,
    I'm going to give this a try, if I can get this to work it's going to save me a ton of time for other projects that I have to do. I can't thank you enough for taking the time to help.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Index/Match ??? (2000 sr1)

    Good luck. Let me know if it doesn't work and I will see what I can do to help. FYI - I used the Machine Data Source for Pervasive and can attach to it using excel or access. For MPM, you need to run a utility that creates a data dictionary to provide a path to the correct project so you can connect to it. If you can't get the machine data source to work, I have another writeup that my help as well.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match ??? (2000 sr1)

    Hi Gary,
    It works like a charm, you have no idea how much time you have saved me. Not only that but there are other reports that I can do now that I couldn't do before.
    Thanks again for your help.
    David

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Index/Match ??? (2000 sr1)

    David,

    Glad I was able to help.

    Gary
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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