Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas (Excel 2003)

    Let's see if I can explain my situation. Attached is a spreadsheet I I have to use to track the quanities issued and received. What I am trying to figure out is I would like the QTY REC column to go away and the on-shelf qty be based off of the QTY ISS and QTY ORD columns. I want the on-shelf qty to be updated only when the QTY ORD is received, date filled in. The reason for this is I cycle the dates through out the current year. I track the data for just the current year, over writing the previous year. I hope this is making sence. There are other formulas throughout the spreadsheet that I need to keep in place, as you can see.
    Thanks for any advice or help
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (Excel 2003)

    Do you also want to get rid of H7:H126 and keep only the current QOH, or do you want to keep a history of QOH?

  3. #3
    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: Formulas (Excel 2003)

    If I understand correctly you can use the formula in H5:
    <pre>=SUMPRODUCT($K$10:$K$15*($L$10:$L$15>0))-SUM($E$8:$E$125)</pre>


    and not require the ranges in F or the intermediates in H.

    Steve

  4. #4
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (Excel 2003)

    NO. I want to keep H7:H126. This shows a continuous count of QTY O/H, H column. I want to delete F6:F126. I want the H column to show what I have on the shelf by subtracting the issues in E column plus the QTY ORD column. I want the QTY ORD only to add onto the shelf when the transaction is received with a date, L column.
    I might be asking to much but I thought it might be somewhat of a challenge to some of you out there.
    Thanks for the help.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (Excel 2003)

    Column D now contains dates corresponding to quantities issued and also dates corresponding to quantities received. What should happen to the second category if the quantities received column is removed?

    It might be useful if you posted a sample of what you would want your worksheet to look like.

  6. #6
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (Excel 2003)

    Steve,
    This formula is pretty darn close to what I am looking for. I want to have a continuous count down H column and have the total at the bottom of the column show up in cell H5.
    Thanks. This is looking better all the time.

  7. #7
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (Excel 2003)

    Hans,
    I am wanting my incoming inventory to be calulated from columns J,K,L. Get rid of the F6:F126.
    If you still need an example let me know.
    Thanks again.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (Excel 2003)

    Will you still enter the dates of the quantities received in column D, even if there is no column F?

  9. #9
    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: Formulas (Excel 2003)

    In H8:
    =IF(D8>0,SUMPRODUCT($K$10:$K$15*($L$10:$L$15<=D8))-SUM($E$8:E8),"")

    Copy/Autofill from H8 to H9:H125

    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
  •