6 Replies Latest reply: May 15, 2014 10:05 AM by fatboy02 _ RSS

    External lookup ?

    fatboy02 _

      At the beginning of each month I receive 4 separate Excel spreadsheets. 

       

      The first sheet lists inventory that is currently excess and obsolete (E&O). 

       

      The second spreadsheet lists inventory that will become E&O the next month but also includes the data from spreadsheet one.

       

      The third spreadsheet lists inventory that will become E&O in 2 months but also includes data from the previous 2 spreadsheets.

       

      Spreadsheet 4 follows the same logic.

       

      My need is to generate a report that gives me:

       

      Only new E&O items for:

       

      Next month

      2 months out

      3 months out

       

      Getting New E&O for next month isn’t a problem as I first opened spreadsheet 2 and did an external lookup against spreadsheet 1and was able to extract the items that were on spreadsheet 2 but were not on spreadsheet 1. 

       

      I can do the same thing to get 2 month E&O by opening spreadsheet 3 and using sheet 2 for an external lookup.  Likewise for the 3 month data.

       

      I’m sure this isn’t the most efficient way of getting the data I need and, given the fact that I am a Monarch newbie, I thought I would present this to the group and see how you all would approach mining this data. . .

       

      Hope this makes sense and thanks in advance for any suggestions. . .