Extract Data /Look up Data / To get data automatically by refering invoice number

[PMH2384]

Dear All,

I would like ask for help for the following formulas. I have tried to create by learning , however, I couldn't get the solution.

Could someone help me please if these will possible to create it ?

I would like get automatic data ( look up data ) by referring invoice number .
Attached is sample file .

1) Inv Details Sheet

2) Inv Summary Sheet : Wanted to get combine data of multiple item names from "invoice Details sheet" to "Invoice summary Description"
3) Inv Format Sheet : Wanted to get automatically "Batch Number" and" Expiry Date " data under item name of the invoice by looking up invoice number.

Kindly guide me , please. Thanks in advance for your valuable time .

[PFDave]

Hi this file wont open for me, could you try and attach again please?

[Glenn Kennedy]

Do you require backward compatability with Excel 2003? I ask because you are using the out-of-date xls extension....

[Glenn Kennedy]

Take a look at this. Is it what you want? I'm happy to explain what's going on. However, your data layout in the invoice is pretty horrible - with different formulae in alternate rows. Much easier if it was all on one row....

[PMH2384]

Dear Glenn,

Yes, this is what I want. kindly guide me, please . I think concatall is not in excel build formula.
I am willing to change data layout formula in the invoice for much better formula .

I don't need backward compatibility with Excel 2003, can convert to xlsx version as well .

Thanks again ..

[Glenn Kennedy]

OK. Back again. For Invoice summary, I pasted this UDF code (shamelessly copied from Tigeravatar) into a module (right click on sheet name)

Please Login or Register  to view this content.

It takes the form:

=concatall(IF('Inv Details'!$A$5:$A$198='Invoice Summary'!A6,'Inv Details'!$F$5:$F$198,""),", ")

Syntax: = ConcatAll(if(range=criterion, TRUE - do this, FALSE - do that,separator).

On Invoice summary, I killed a lot of columns and put 1 line = 1 item. Much easier to copy formulae down. You can always set the print/pdf area to landscape to accommodate this change. Your Unit Prices aren't there, so I couldn't fix it up for you. Finally, saved as modern xlsx.

TaDaa!

Done. Any Q's, just shout.

[PMH2384]

Hi Gleen,

Firstly, my apologizes for late reply as just came back from long leave. Really appreciate for your help .
I have managed to learn from your file and followed as the same, Thanks to your well guide.

Could I ask one more question ?

And , in Invoice format " Unit Price G", I am using Sumifs function of "SUMIFS('Inv Details'!O:O,'Inv Details'!A:A,"="&'Invoice Format'!$H$5,'Inv Details'!F:F,"="&'Invoice Format!D22)"
But, this doesn't work if there is two batches under the same name "Colunm H" in Inv Details Sheet.

Same for Column C in invoice format sheet . Attached is the file. Hope you could help me in this one as well.

Sincerely thanks again.

[Glenn Kennedy]

Take a look at this one. Does this do all that you need?

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[PMH2384]

Yeah, this did all of my needs. Thank you so much again.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.