Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monthly Spreadsheet (Excel 2003)

    We need to link a spreadsheet (#2) that is going to pull data from another spreadsheet (#1) and analyze it. The problem is that at the end of every month we are going to save spreadsheet #1 as the last month and then delete any closed items for the next month so that the "working" file only has open items on it. What is the easiest way to get the 2nd spreadsheet (the spreadsheet that pulls info from spreadsheet one) to do what it needs to do every time a new month comes along without going into the 2nd spreadsheet and changing it manually?

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

    Re: Monthly Spreadsheet (Excel 2003)

    What does the 2nd spreadsheet need to do when you delete the closed items? Are the two spreadsheets in the same workbook or two different workbooks? What does the 2nd spreadsheet do when you add new open items during the month? Are you manually changing it to account for the new items? Could you upload a workbook that shows what you are doing?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    May 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monthly Spreadsheet (Excel 2003)

    The first spreadsheet is a list of open purchase orders with actual lead time vs quoted lead time- along with other info such as part number po # etc.
    The second spreadsheet will be rating the supplier on their delivery. So each month will need to have both spreadsheets.
    The 2 spreadsheets will be in different workbooks because spreadsheet #1 will be on the public file.
    I do not have the 2nd spreadsheet created yet.

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

    Re: Monthly Spreadsheet (Excel 2003)

    I don't think your question can be answered until you know what the second sheet will be doing. Most likely, you can use If statements in the formulas in sheet2 to get the data from sheet 1. If it is set up correctly, the IF statements will pull the data only when it is there, and you sould not have to do anything when you delete data. Just as a starting place, a formula like the one below might be used to get data from sheet1:

    <pre>=IF([Book1]Sheet1!$A$1="","",[Book1]Sheet1!$A$1)
    </pre>

    Legare Coleman

Posting Permissions

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