# Populating Vouchers With Purchased Items For Auction

1. ## 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.

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.

2. ## 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. ## Re: Populating Vouchers With Purchased Items For Auction

Originally Posted by SPARKY347
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. ## 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. ## 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

6. ## 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

7. ## Re: Populating Vouchers With Purchased Items For Auction

Originally Posted by SPARKY347
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)

8. ## 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. ## 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

10. ## 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

11. ## Re: Populating Vouchers With Purchased Items For Auction

Originally Posted by SPARKY347
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!

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

#### 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