PDA

View Full Version : Solved: Create Invoice from datalist



tlchan
11-08-2008, 10:15 PM
Hi there,

I would like to create a invoice from my customer data using date as criteria to pull data to the invoice. My customer data is in sheets("dalalist") and invoice in sheets("invoice").


How can I to pull all items matched in F5 of sheets("Invoice") with sheets ("Dalalist") using VLOOKUP function to list out in the invoice ?




Workbook attached.

GTO
11-09-2008, 03:22 AM
F5 appears to be blank on both sheets. Please explain or show what a result would look like, as well as of course, where the entered variable data is to exist.

Thank you so much,

Mark

tlchan
11-09-2008, 07:10 AM
I'm sorry the criteria range is G2 instead of F2 in sheet ("invoice"). Lookup for matched criteria in range("D7:D10") in sheets("Datalist").

Attached sample workbook

georgiboy
11-09-2008, 09:36 AM
I am not sure how to achieve this with vlookup alone but this code seems to do the trick

Sub Invoice()
Dim rCell As Range, MyRange As Range
Dim x As Integer
x = 17

Set MyRange = Sheets("Datalist").Range("D7:D" & _
Sheets("Datalist").Range("D" & Rows.Count).End(xlUp).Row)

For Each rCell In MyRange.Cells

If rCell.Value = Sheets("Invoice").Range("G2").Value Then
Sheets("Invoice").Range("B" & x).Value = rCell.Offset(, -2).Value
Sheets("Invoice").Range("C" & x).Value = rCell.Offset(, 1).Value
Sheets("Invoice").Range("D" & x).Value = rCell.Offset(, -1).Value
Sheets("Invoice").Range("E" & x).Value = rCell.Offset(, 2).Value

x = x + 1

End If
Next

End Sub

Hope this helps

rbrhodes
11-09-2008, 10:30 PM
Hi folks,

I'd be tempted to put this in as a Worksheet_Change event based on cell G2 changing. See attached.

GTO
11-10-2008, 01:41 AM
Greetings,

Well, I have used VLOOKUP exactly zero times; but in looking at the help topic, the first thing I notice is that it requires using the leftmost column for the 'search area' so-to-speak.

After changing that around, I then noted that you have serial and account numbers switched around. As it appeared that you might be looking for a formula based answer, and I certainly could learn much in this area...here's a formula based answer. I would suspect, or actually hope, that it gets picked apart a bit, as I'm sure it can be improved on.

In short, the first primary formula is:
{=IF(NOT(ISNA(MATCH($G$2,Datalist!$B14:$B$20,0))),OFFSET(INDIRECT(ADDRESS(M ATCH($G$2,Datalist!$B1:$B$20,0),3,1,1,"Datalist")),0,0,1,4),"")}

...with a helper of:
=MATCH($G$2,Datalist!$B1:$B$20,0)

and a secondary primary formula of:
{=IF(NOT(ISNA(MATCH($G$2,INDIRECT(ADDRESS($F17+1,2,1,1,"Datalist")&":B20"),0))),OFFSET(INDIRECT(ADDRESS(F18,3,1,1,"Datalist")),0,0,1,4),"")}

...with a helper of:
=MATCH($G$2,INDIRECT(ADDRESS($F17+1,2,1,1,"Datalist")&":B20"),0)+$F17

The secondary formulas (primary and helper) can be 'drug' down.

The only downside (besides that I'm sure there must be increased efficiency easily obtainable, but for lack of knowledge), is that I ended up with zeros being displayed. While of course you could simply choose not to display zero values, I left them visible, as I figure the formula should really handle this as well.

Well, there you go?:bug:

Mark

tlchan
11-10-2008, 10:04 AM
Hi all,

Thanks all for your assistance. All solutions work fine for me.However rbrhodes's solutions on worksheet change event with counter No and clear of old entries in invoice is exactly I'm looking for.

Thanks again :hi: