Results 1 to 6 of 6

Thread: Advice Please!

  1. #1
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advice Please!

    Let me start by saying that I am a VBA novice. I am using Excel 97 (SR1). I have been viewing the WOPR Lounge for a while and you friendly, helpful people have given me the courage to attempt my first project.

    Attached is an example (Grain Coverage Report) which illustrates the format the User would like to see. The User inputs the budget information and the Grain Department inputs the purchase information. Currently, all the data in the "Budget vs. Actual" section is manually updated based on the data entered in the "Purchases" section. What I would like to do is make everything in the "Budget vs.Actual" section update automatically as information is entered into the "Purchases" section.

    Please give me your suggestions on what you think is the best approach (formulas, macros, pivot tables, etc.) to achieve the desired result.

    Thanks in advance for your consideration and invaluable help.
    Attached Files Attached Files

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

    Re: Advice Please!

    I have attached a modified version of your spreadsheet that I think does what you wanted. It assumes that you will be adding additional P.O.'s up through row 100. I had to delete your hidden row 26 for this to work. This sheet uses array formulas to calculate Purchased, Average Price, and Delivered, and normal formulas to calculate Unpurchased and Undelivered. I also added a new column that calculates the number of loads in column AU.
    Attached Files Attached Files
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice Please!

    WOW! I didn't expect such a complete solution in such a short time. I will work with it in the next day or two to make sure I didn't overlook anything. I will report back to you when I get finished to say "thanks" again.

  4. #4
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice Please!

    I have discovered a situation that my simplified example did not illustrate. In my example, the "Delivery Period" (A7:A10) in the "Budget vs Actual" section and the "Delivery Period" (D2124) in the "Purchases" section always follow the same quarter-by-quarter format. In real life, the "Delivery Period" in the "Purchases" section could be as short as one month (JAN01) or even extend beyond the typical quarter year (JFMAMJ01). Any suggestions you might have on how to deal with this issue would be much appreciated.

    Thanks again Legare.
    Attached Files Attached Files

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

    Re: Advice Please!

    Well, the delivery all in one month seems to be pretty easy since one month is in a quarter. You just specify that quarter.

    If the delivery streaches across multiple quarters, the best suggestion I can come up with is to use multiple rows with the same PO number showing how much is to be delivered in each quarter. A second possibility would be to have four delivery columns, one for each quarter, showing the expected delivery quanity for each quarter. You could also have 12 columns and show expected delivery by month and have the formulas calculate the quarterly. If you know exact expected delivery dates, you could also list those and have the formula sort out the quarters. There are many possibilities. It depends on what the data is as to how to best arrange it. One thing that you can't do. If the expected delivery is accross quarters, and you can't tell how much is in each quarter, you can't calculate quarterly deliveries.
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Jan 2003
    Location
    Omaha, Nebraska, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice Please!

    Using multiple rows for the same PO was my first inclination. Only two or three people have access to the spreadsheet so it shouldn't be too hard to get everone to do that.

    Thanks again for all your help.

Posting Permissions

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