VB Code required for one cell with multiple results

[Anbuselvam K]

In the attached sheet, I got multiple results in the sheet COA and cell B8, B10 and B16.

To get the results I have a helper column in the sheet Master Data Column M, N and O.

How can I avoid those helper columns to get the results in the sheet COA and cell B8, B10 and B16 as like now?

Please suggest the code which can eliminate all other formulas.

Note:
Column B, C and H are Production date, Product Name and Production quantity

Column J and L are sales date and sales quantity

In the sheet COA,

Cell B8, B10 and B16 are Quantity, Batch number and Production date which is in the stock (Produced but not yet sale to anyone)

Cell B4 drop-down list is the product names. If I select a product from the list, then B4, B10 and B16 must display the stock of the selected products from the data of Master data.

Thanks in advance

 

[Anbuselvam K]

Hi

I got the code as below. it is working well.

Function GetData(ProductRng As Range, Criteria As String, InvoiceRng As Range, ResultRng As Range) As String


Dim T As Long


For T = 1 To ProductRng.Cells.Count


If ProductRng.Cells(T, 1) = Criteria And InvoiceRng.Cells(T, 1) = 0 _
And InvoiceRng.Cells(T, 1) <> "" Then
GetData = GetData & ", " & ResultRng.Cells(T, 1)
End If


Next T
If GetData <> "" Then GetData = Mid(GetData, 2)


End Function

 

[vletm]

Anbuselvam K

Is this solved or not?

Was this crossposted ?
or
what did You mean about 'I got the code ...'?

 

[Anbuselvam K]

Hi

I was trying to delete this post as I got the desired code from another forum but I did not find the option to delete it. That's why I put the code here to avoid others working on this post.

 

[vletm]

Anbuselvam K
Three questions ... how many answers?

Is this solved or not?
... Maybe solved?
Crossposted:
... You should reread:
https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
especially part of Cross-post.
... and I'm 99% of sure that other forum has same kind of rules.
'I got a code ...'
That's good that You share needed code here too.

 

[Anbuselvam K]

The code is working and gives desired results. Please check the attached file for your information.

I do not waste others time to working on the answered post. This is what I shared the code immediately.

 

[vletm]

Anbuselvam K
You needed that code ... not me ... so I no need to test.

Between Your original post and Your cross-post answer post were 22minutes.
I could write that I or someone else would start working cause this case between those ... and ... I or someone else would get own solution -- just now or later.
If someone really need to use cross-post
then that should let be known in the first post!
Then others than You,
could know that they could do something which, even You, won't need/use.