Filling a few cells based on the info from another worksheet.

[Bruce]

I am creating an invoice. The data is to be pulled from a worksheet called
"DATA".
There is one row for each item. There may be several items per invoice. Each
are keyed with the "InvoiceNum" columnBA
On my invoice, I have named fields that represent each item on a row. But I
am not sure how to get the other items that are tied to the same invoice
number. There are only 2 cells that are needed and these are item and rate
(columns AU and AV)

Any ideas on how to do this?

I am using the example I D/L from http://j-walk.com/ss/excel/tips/tip92.htm
as my base.

Thanks
Bruce

 

[Gord Dibben]

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

 

[Bruce]

Gord,

I am sorry if I am not quite understanding how this would help. I am fairly
familiar with vlookup(). This would give me one of several choices. What I
am wanting is to fill several rows(on WS forms) with ALL of the records that
matches the invoice number (from WS data).


Thanks
Bruce

 

[Gord Dibben]

Bruce

I think I don't have a clear understanding of what you need.

You referenced the site for John's Elephants R Us mailmerge.

You have downloaded his sample workbook and are trying to adapt it to your
needs, right?

I'm not sure if that particular workbook is what you need for Invoices and how
to adapt it without re-working it.

Have you gone carefully over the Help sheet that John provides with the
workbook?


Gord

 

[Bruce]

In WS DATA, I have rows of data that contain different services I have
performed.
This data is structured like this
orderdate ServiceDate CustName..... JobItem(AU) JobPrice(AV) ...
InvoiceNum(BA)
1/23/07 1/25/07 Joe Doe Service1 150.00
12307-1a
1/23/07 1/25/07 Joe Doe Service2 125.00
12307-1a
1/23/07 1/25/07 Joe Doe Service2 125.00
12307-1a
1/23/07 1/25/07 Bob Smith Service1 150.00
12307-1b

For Joe Doe,
I would need to create someting like the following on FORMS
=INDIRECT("Data!aU" &RowIndex)
=INDIRECT("Data!aU" &RowIndex + 1) 'if this is the same invoicenum
=INDIRECT("Data!aU" &RowIndex + 2) 'if this is the same invoicenum
etc...
and then name each of these Invoiceitem1, Invoiceitem2, Invoiceitem3 etc...

=INDIRECT("Data!aV" &RowIndex)
=INDIRECT("Data!aV" &RowIndex + 1) 'if this is the same invoicenum
=INDIRECT("Data!aV" &RowIndex + 2) 'if this is the same invoicenum
etc...
and then name each of these InvoicePrice1, InvoicePrice2, InvoicePrice3
etc...

I am wondering if something like
=INDIRECT("Data!aV" &RowIndex)
=if(INDIRECT("Data!BA" &RowIndex +1) = INDIRECT("Data!BA" &RowIndex ),
INDIRECT("Data!aV" &RowIndex + 1),"")
=if(INDIRECT("Data!BA" &RowIndex +2) = INDIRECT("Data!BA" &RowIndex ),
INDIRECT("Data!aV" &RowIndex + 2),"")
=if(INDIRECT("Data!BA" &RowIndex +3) = INDIRECT("Data!BA" &RowIndex ),
INDIRECT("Data!aV" &RowIndex + 3),"")
etc

and
=INDIRECT("Data!aU" &RowIndex)
=if(INDIRECT("Data!BA" &RowIndex +1) = INDIRECT("Data!BA" &RowIndex ),
INDIRECT("Data!aU" &RowIndex + 1),"")
=if(INDIRECT("Data!BA" &RowIndex +2) = INDIRECT("Data!BA" &RowIndex ),
INDIRECT("Data!aU" &RowIndex + 2),"")
=if(INDIRECT("Data!BA" &RowIndex +3) = INDIRECT("Data!BA" &RowIndex ),
INDIRECT("Data!aU" &RowIndex + 3),"")
etc

There may be 1 item or upto 15...


Thanks
Bruce

 

[The Great Attractor]

Would that not be VLOOKUP used in accordance with HLOOKUP then?