 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007new user needs help
Hi, I have a stocklist (sheet 2) with 4 columns: A= LOT # B=DESCRIPTION C=BIDDER # D=PRICE, and an invoice (sheet 1) with column A=LOT # containing this formula: IF(A16="","",VLOOKUP(A16,STOCKLIST!$A$2:$B$501,2.0)) so the description comes up automatically in the invoice. Now what i want to do is have an invoice generated automatically when I enter the bidder number and price paid into their respective columns, with bidder no.5 generating an invoice numbered 1005. Row 1 has the invoice no, row 2 has the bidder no. Is this possible? Thank you  SignatureRegards, Sarah Actually, upon further consideration, what I need is a formula that will enter the information from column A (LOT #), B (DESCRIPTION) from STOCKLIST to INVOICE when I enter the BIDDER # (COLUMN C) and the PRICE (COLUMN D). It's for an auction, and one bidder may purchase serveral lots. I also need the invoice to save as I enter the above information into the stocklist. I will manually set up invoices as each person registers, giving them a bidder number, and entering their personal details into an invoice. SignatureRegards, Sarah > Hi, > [quoted text clipped - 8 lines] > > Thank you Hi Sarah Advanced Filter, extracting the relevant rows to your invoice sheet will achieve this. Take a look at Debra Dalgleish's site for more information. http://www.contextures.com/xladvfilter01.html#ExtractWs There is a downloadable file that you could modify. http://www.contextures.com/excelfiles.html#Filter SignatureRegards Roger Govier > Actually, upon further consideration, what I need is a formula that > will [quoted text clipped - 28 lines] >> >> Thank you Hi Roger, Thanks for you response, I can't get it to work for me though....Perhaps I'm missing something out? Or doing something wrong, most likely.... Or maybe I didn't explain my problem very well. What I need to have happen is: when I enter a bidder number into column C in sheet 2, named STOCKLIST (next to the lot number, column A, and the matching description, column B), and the price the bidder paid for the lot into column D, I want the information from columns A,B & D to be entered into that bidder's invoice (they may buy lot number 8 -row 12-and lot number 246-row 248), and I want the invoice to be saved with that information. Is this possible with Advanced Filters? SignatureRegards, Sarah > Hi Sarah > [quoted text clipped - 38 lines] > >> > >> Thank you Hi Sarah Yes, advanced Filter can extract data in that way. You must start the filter from the Destination sheet. --Regards Roger Govier > Hi Roger, > [quoted text clipped - 65 lines] >> >> >> >> Thank you Hello again Roger, I really do appreciate your help, however I seem to be having a great deal of difficulty in following the instructions.....I just can't make it work. SignatureRegards, Sarah > Hi Sarah > [quoted text clipped - 74 lines] > >> >> > >> >> Thank you Hi Sarah If you want, you can mail me the file and I will see if I can set it up for you. To mail direct roger at technologyNOSPAM4u.co.uk remove NOSPAM from address to send and do the normal thing with "at" SignatureRegards Roger Govier > Hello again Roger, > [quoted text clipped - 96 lines] >> >> >> >> >> >> Thank you Hi Sarah File received and returned. I think it was merged cells on your invoice sheet that was causing your problem. They are usually a pain for most things. I have got rid of all merged cells. As you have a specific layout for your invoice, I have used Advanced Filter to copy the relevant lines to a Temporary Sheet and then copied them from there to your Invoice, using Paste Special so as not to change the formatting. I have attached the code to a button on your Invoice sheet. The code is as follows Sub CreateInvoice() Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet Set wss = ThisWorkbook.Sheets("Stocklist") Set wsd = ThisWorkbook.Sheets("Invoice") Set wst = ThisWorkbook.Sheets("Temporary") Application.ScreenUpdating = False wsd.Range("A16:D34").ClearContents wst.Range("A4:D27").ClearContents wst.Range("D2") = wsd.Range("b2").Value wss.Range("A3:D28").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=wst.Range("D1:D2"), CopyToRange:=wst.Range("A4:D4"), Unique:=False wst.Range("A5:D7").Copy wsd.Range("A16").Select Selection.PasteSpecial (xlValues) Application.CutCopyMode = False wsd.Range("B2").Activate Application.ScreenUpdating = True End Sub SignatureRegards Roger Govier > Hi Sarah > [quoted text clipped - 105 lines] >>> >> >> >>> >> >> Thank you Thank you Roger, you are amazing! SignatureRegards, Sarah > Hi Sarah > [quoted text clipped - 143 lines] > >>> >> >> > >>> >> >> Thank you |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.