+ Reply to Thread
Results 1 to 11 of 11

Populating Vouchers With Purchased Items For Auction

  1. #1
    Registered User
    Join Date
    10-22-2015
    Location
    Idaho
    MS-Off Ver
    2013 Pro Plus
    Posts
    4

    Populating Vouchers With Purchased Items For Auction

    Hello, all!

    A local youth group is hosting an auction next month, and I have been tasked with creating the spreadsheet for recording winning bidders and winning bids, and making redemption vouchers to give to the winning bidders once they've paid.

    Here's what the Excel document I have set up looks like:
    Worksheet 1 columns: Item #(1), Item Description (2), Winning Bidder ID # (3), Winning Bid (3)
    Worksheet 2 columns: Bidder ID # (1), First Name (4), Last Name (4)

    (1) = Sequentially increasing from 1 to n, populated prior to event
    (2) = Entered prior to event
    (3) = Recorded when silent auction closes/during live auction
    (4) = Recorded as people enter auction

    What it sounded like they are wanting for the vouchers is just to print off a bunch, and write down on each what item numbers they won as the winning bidders check out (from there they'll take it over to another station, and use the voucher to redeem their items). What I'm planning on doing for this is setting up a mail merge in Word that pulls all the names and bidder ID #'s and automatically puts them on the vouchers.

    Now, all that is simple enough to do. Here's where it starts to get complicated:

    Initially I had been thinking of it populating all the fields in the "Cards.docx" file attached, and calculating the total amount owed so that the people checking out bidders can do this more quickly. However, the following would work.

    In order to expedite the overall process, I'd like to make the vouchers automatically pull the list of items over from the spreedsheet.

    I've got an idea of how this could work, but haven't a clue what tools to use to accomplish it:
    Inside Excel, in worksheet 2, a third column would look at what the bidder ID # is, match that up with winning bidder #'s in worksheet 1, and list them all in the same cell (appearing something like "1, 52, 173"). From here it's a simple merge field added to the voucher Word document.

    Any ideas what tools I could utilize? I've used Excel formulas in the past, but am unsure as to whether they have this capability. I'd imagine I will need to delve into macros, which is beyond the scope of my experience, but am more than willing to learn if pointed in the right direction.

    Thanks in advance!
    ElectroPulse

    EDIT: Attached is an example spreadsheet, and "cards.docx" is what I had initially been picturing as what the vouchers would look like, but am thinking that would be a bit complex for this.
    Attached Files Attached Files
    Last edited by ElectroPulse; 10-22-2015 at 09:33 PM.

  2. #2
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    I'm sure if you created a sample work sheet and populated it with some sample information that someone here could whip this up very fast for you

  3. #3
    Registered User
    Join Date
    10-22-2015
    Location
    Idaho
    MS-Off Ver
    2013 Pro Plus
    Posts
    4

    Re: Populating Vouchers With Purchased Items For Auction

    Quote Originally Posted by SPARKY347 View Post
    I'm sure if you created a sample work sheet and populated it with some sample information that someone here could whip this up very fast for you
    Thanks for the suggestion! Attached them

  4. #4
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    so ive got a bunch done for you but I cant seem to load my file through here

  5. #5
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    so give this a try its done with VBA and macros and not fully finished but you can try to populate the "items" sheet with the bidder number and the winning bid $. once both are populated for each item they will transfer to the "bidders" page and turn green to let you know they are not completed with both a bidder number and a price. on the bidders page you will only need to assign a number first and last name, as you complete the "items" sheet the transferd data will expand each bidders purchased items and color code each bidder for easy identification, give it a try let me know what you think I'm still working on a few more things for it
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    heres what I have so far everything between the bidders and the purchased items is automated and will automaticly update including totals for each bidder
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-22-2015
    Location
    Idaho
    MS-Off Ver
    2013 Pro Plus
    Posts
    4

    Re: Populating Vouchers With Purchased Items For Auction

    Quote Originally Posted by SPARKY347 View Post
    heres what I have so far everything between the bidders and the purchased items is automated and will automaticly update including totals for each bidder
    Sorry about the delayed response, some things came up.

    Thank you so much for working on this, it looks great! This is exactly the type of thing I'm looking for

    I'm curious though, the prices are updating correctly, but when changing which bidder won an item, that isn't updating. Is there a manual "Update" button to make it refresh that? (additionally, adding items is producing errors)
    Last edited by ElectroPulse; 10-25-2015 at 05:30 PM.

  8. #8
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    I'm still working on it. I have an updated copy that works good. except if all the info for the particular item is filled in and the row is green it means it has transferred to that particular bidder. You are able to change the price but not the bidder number. Basicly once the bidder number is inserted into a item it will stay that way. Ill work on revising the code but it will take a while. It could be a few more days as my work shifts are starting agin. I will load my latest version this evening that works good

  9. #9
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    here try this its not perfect but I'm still learning vba, the only thing this will not do it update the "bidders" page if you change the winning bidder on "items" after the row is green. I can change it but again wont have the time for a few days. try populating the fields in this newest wworkbook, try by putting a handful of item numbers and there descriptions, like you were setting up the auction, next add some bidders, as if they were walking through the door and registering, next start filling in the winning bids on the "items" page as if the bidders starting winning auctions and let me know how it works out

    NOTE: IF ANYONE ELSE VIEWING THE THREAD HAS ANY IDEAS/CHANGES PLEASE FEEL FREE AS IM STILL LEARNING
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    05-02-2015
    Location
    calgary alberta
    MS-Off Ver
    2012
    Posts
    205

    Re: Populating Vouchers With Purchased Items For Auction

    this is my latest attempt, you should be able to update items and what not, let me know how this works, please give it a full test as I'm not really perfect with my VBA yet
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-22-2015
    Location
    Idaho
    MS-Off Ver
    2013 Pro Plus
    Posts
    4

    Re: Populating Vouchers With Purchased Items For Auction

    Quote Originally Posted by SPARKY347 View Post
    this is my latest attempt, you should be able to update items and what not, let me know how this works, please give it a full test as I'm not really perfect with my VBA yet
    Hmm... I gave it a try, and it threw a couple of syntax errors (on line 72 and 73). I'm not at all familiar with VBA, but was able to look at other similar lines to work out what was wrong to get it running. The lines were as follows:

    Set IID = Worksheets("bidders").Range("a:a"Row).Find(what:=NewID)
    NEWROW = Worksheets("bidders").Rows(IID. + 1).Row

    I changed them to:
    Set IID = Worksheets("bidders").Range("a:a").Find(what:=NewID)
    NEWROW = Worksheets("bidders").Rows(IID.Row + 1).Row

    I tested it, and it didn't seem to update... I tried changing price, as well as bidder number. One thing to note, though, is if I switch the color of the completed items from green to white, it allows me to re-enter it. However, doing so creates a duplicate entry (it leaves it in the original bidder, and adds it to the second one).

    Admittedly, my changes may be the cause of why it isn't updating.

    Thank you again for putting in time on this! Once complete, this will definitely make auction processing go much smoother!

+ 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. Replies: 5
    Last Post: 07-29-2014, 02:11 PM
  2. populating a list of items depending upon whether cells have values
    By jase366 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-03-2013, 02:18 AM
  3. Replies: 2
    Last Post: 04-29-2013, 05:18 PM
  4. Populating a list box with items from cell
    By HikariHitachi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2013, 04:13 PM
  5. [SOLVED] Populating an array with selected ListBox items
    By wz4np1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 07:52 AM
  6. Populating Treeviws Child Items
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2010, 12:40 PM
  7. Problem: populating listbox items
    By ilyaskazi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2005, 07:22 AM

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