new user needs help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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?
 
Hi Sarah

Yes, advanced Filter can extract data in that way.
You must start the filter from the Destination sheet.

--Regards

Roger Govier


Sarah said:
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?
Click to expand...
 
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.
 
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"
 
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
 
Back
Top