+ Reply to Thread
Results 1 to 9 of 9

Creatung a Purchase Order packet, need help!

  1. #1
    Registered User
    Join Date
    08-10-2017
    Location
    Massachusetts
    MS-Off Ver
    2016
    Posts
    6

    Creatung a Purchase Order packet, need help!

    Thanks for checking this post!

    As stated, I'm creating a Purchase Order packet (Excel 2016), which generates a printable Requisitions and POs, and allows fields for delivery and invoice tracking. On the entry sheet (PO Long Form), I've created a table from columns A$-G$, which will contain the individual PO line items (Line#, QTY, UOM, Part#, Description, Unit $, Extended $); column G holds a simple formula (F$*B$) to calculate Ext $ (column G).

    I've created a child sheet (PO) which takes some of the entry data and auto-plugs it in (=('PO Long Form'![cell]) or the like) to enter in supplier, address, etc. This works fine.

    However, in the PO itself I'm trying to have the table data update the cells in which the line items need to go. EX: D20-J20 will (hopefully) display any line items entered in the table range above (A$-G$), and stop when no more data is present. So, if I have three line items, it will fill D(20-22)-J(20-22). I have formulas at the bottom to SUM the Subtotal, Tax, Shipping into a TOTAL. If there are 20 line items, D(20-39)-J(20-39). And so on ...

    Without going into the tedious details of describing all the ways I've done it WRONG, are there any suggestions out there on how to do this RIGHT?

    Any and all help is greatly appreciated! Thanks in advance.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Creatung a Purchase Order packet, need help!

    Hi Scott and welcome to the forum,

    I'm imagining that you want blank rows on the invoice form if there are no more items. So you have 20 possible lines on the invoice but only 3 items being purchased. I think you are looking for the

    =IfError( YourFormula , "") type of function.

    This will put a blank in that cell if there is no match for your lookup.

    To better answer your question we really need to see a sample of your workbook.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    08-10-2017
    Location
    Massachusetts
    MS-Off Ver
    2016
    Posts
    6

    Re: Creatung a Purchase Order packet, need help!

    I joined the forum after returning home from work (yay. work from home.)

    I'm having difficulty with the initial phase of this (such a n00b using the table functions). I've tried several different ways to do so, and each time it results in some wonky display of information. I'd thought using a table method would be quicker and cleaner (null values wouldn't carry over, setting a link in the "home" cell on the PO sheet would allow for quick right-and-down fill, etc) but it's not doing any of that. I must be doing it wrong (duh). Should I use a tablevor a script?

    If it goes that long I'll post a sample in the AM.

  4. #4
    Registered User
    Join Date
    08-10-2017
    Location
    Massachusetts
    MS-Off Ver
    2016
    Posts
    6

    Re: Creatung a Purchase Order packet, need help!

    Hang on - can remote into my WS ...

  5. #5
    Registered User
    Join Date
    08-10-2017
    Location
    Massachusetts
    MS-Off Ver
    2016
    Posts
    6

    Re: Creatung a Purchase Order packet, need help!

    https://1drv.ms/x/s!Ap1fp-eanyuKnE3EZp_YzL0qp30O

    that links to the file. I cleaned up any identifying or sensitive info.

    [edit] ugh. hold, please.
    Last edited by ScottCRRC; 08-10-2017 at 09:19 PM.

  6. #6
    Registered User
    Join Date
    08-10-2017
    Location
    Massachusetts
    MS-Off Ver
    2016
    Posts
    6

    Re: Creatung a Purchase Order packet, need help!


  7. #7
    Registered User
    Join Date
    08-10-2017
    Location
    Massachusetts
    MS-Off Ver
    2016
    Posts
    6

    Re: Creatung a Purchase Order packet, need help!

    top shot is PO LONG FORM ITEM, bottom is PO LONG FORM OUTPUT

    [EDIT] I'd upload the file but all I can get are screen grabs

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Creatung a Purchase Order packet, need help!

    Hi Scott,

    I tried to open your OneDrive shared file and it says it is larger than 5MB

    If you can create a simple example and show what you have and what you want, AND attach it to this thread, someone can help.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Creatung a Purchase Order packet, need help!

    It's a big file - I did open it, and can't really see why it is so huge, since it contains little more than the screenshots show. I didn't keep it open.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. 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

Tags for this Thread

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