+ Reply to Thread
Results 1 to 6 of 6

Purchase Order Description

  1. #1
    Registered User
    Join Date
    08-25-2021
    Location
    USA
    MS-Off Ver
    2007
    Posts
    3

    Purchase Order Description

    Hello, I hope all are well. I have little query if someone can solve it.

    I want the description tab of the purchase order to be populated if someone enters the order quantity into the master tab. I was using IF formula (I.e. IF(Master!K20>=1,Master!A20,"")&IF(Master!K21>=1,Master!A21,"")&IF(Master!K22>=1, Master!A22,"")&IF(Master!K23>=1,Master!A23,"") but it is adding more than one description line when customer enter order quantity against more than one item in the master tab. I want the formula to select only one description in a single row and another order in the second row.

    Currently, when buyer putting order quantity for more than items into the master tab. The descriptions are adding with each other in a single line like this (Botanical Disinfectant Solution 55 galBotanical Odor Neutralizer 4/1 gal - Enzyme) - These are two items, not one. Please enter the quantity into the master tab of the attached file and see the result in the agreement tab.

    Please help. Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Purchase Order Description

    I don't see a description tab.
    It might be easier for someone to help if we could see some manually place results with which we could compare the results of our formulas/code.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-25-2021
    Location
    USA
    MS-Off Ver
    2007
    Posts
    3

    Re: Purchase Order Description

    Thank for your reply. See, In agreeement tab, there is a purchase order. Description at A20 in Agreement tab(Sheet). I just want when someone enter order quantity in Column "K" of master sheet description row should automatically be populated. Currently I am using If formula which is adding description in the single line . For example, when some one enter order quantity in front of any one item. It is good till then but when someone want to add another order by putting order quantity in front of any other item, it is being messy by adding two descriptions in the single line "A20" of agreement tab.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Purchase Order Description

    Welcome to Excel Forum Fahad12344
    There are possibly more elegant ways to do this, perhaps using VBA (of which I know too little to help), however since this has not received a response I'll make a formula based proposal.
    1. Add rows to tblData
    2. Populate A20:A35 using: =IFERROR(INDEX(Master!A$20:A$134,AGGREGATE(15,6,(ROW(Master!A$20:A$134)-ROW(Master!A$19))/(Master!K$20:K$134<>""),ROWS(A$1:A1))),"")
    3. Populate B20:B35 using: =IF(A20="","",SUMIF(Master!$A$20:$K$134,Agreement!A20,Master!$K$20:$K$134))
    4. Populate C20:C35 using: =IF(A20="","",SUMIF(Master!$A$20:$K$134,Agreement!A20,Master!$G$20:$G$134))
    5. Populate D20:D35 using: =IF(A20="","",B20*C20)
    6. Populate D36 using: =SUM(D20:D35)
    7. Assuming that the agreement will be printed you could then filter tblData using a filter button in D19 and hiding rows with blanks in D20:D35
    8. After the agreement is printed you could then clear the filter in D19 and the values in column K on the Master sheet and then be ready for the next agreement.
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    08-25-2021
    Location
    USA
    MS-Off Ver
    2007
    Posts
    3

    Re: Purchase Order Description

    Great. Thank you very much. It works like a charm. Stay Blessed.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,563

    Re: Purchase Order Description

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populate my Purchase order from a list of Purchase Order numbers and their details
    By Carolbell44 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2015, 07:14 AM
  2. How to connect purchase order and sell order with party ledger and daily day book
    By harisjawed86 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2014, 11:36 AM
  3. Order entry and Purchase Order using VBA
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2012, 12:39 AM
  4. Macro Reqd to update Purchase order log, everytime a new purchase order is made
    By manthankanabar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-23-2012, 01:47 AM
  5. Replies: 10
    Last Post: 09-27-2012, 03:59 PM
  6. purchase order list & order number generator
    By podaf in forum Excel General
    Replies: 2
    Last Post: 06-28-2009, 06:02 PM
  7. [SOLVED] purchase order counter in excel purchase order template
    By Brandy@baoco in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-23-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1