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
--
Regards,
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

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?

--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

Hi Sarah

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

--Regards

Roger Govier


"Sarah" wrote in message
...
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?

--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

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.
--
Regards,
Sarah


"Roger Govier" wrote:

Hi Sarah

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

--Regards

Roger Govier


"Sarah" wrote in message
...
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?

--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

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"
--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Roger Govier" wrote:

Hi Sarah

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

--Regards

Roger Govier


"Sarah" wrote in message
...
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?

--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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"
--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Roger Govier" wrote:

Hi Sarah

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

--Regards

Roger Govier


"Sarah" wrote in message
...
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?

--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah

Thank you Roger, you are amazing!
--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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"
--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Roger Govier" wrote:

Hi Sarah

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

--Regards

Roger Govier


"Sarah" wrote in message
...
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?

--
Regards,
Sarah


"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Sarah" wrote in message
...
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.
--
Regards,
Sarah


"Sarah" wrote:

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
--
Regards,
Sarah