Thread: Returning multiple rows of data based on certain criteria

any ideas? Anybody? Something, please. At least the first part....

Thanks...

Hi Ghurhu

I have had a look at this. I can't understand what you need. Please could you provide some test data in the sample sheet? It is difficult to understand what your input and output are at present. I couldn't see 'abcd' in your attached file.

Best regards, Rob.

Damn! For some reason - the attachment is not the one I prepared for this thread
So - here is a new one.
As said before - the colored (blue and red) fields in the Planned Sheet is entered manually by each respective Department.
Row 3 in the example should be AUTOMATICALLY added when data is added in Row2. In the same way - if data is added in Row4 (for order no. "cdef") - Row 5 should be added automatically, etc.
Also, the purple dates are based on the following info that will be added somewhere in the sheet: FP Type A=13 production days; FP Type B=90 days, etc.
This is for the first part of what i need.

For the second part (sheet 1, Overview) as you can see - at the moment all I was able to do is have the overview show what orders are planned to begin at the respective date. Further more, I need that overview to show NOT ONLY when an order begins being produced, but show it in the overview in each day of the production process, based on the number of days defined by FP Type. So, in the attached example - order "adcd" should be shown from Jan 1st to Jan 14th, order "cdef" from Feb 1st to May 1st, etc.

N.B.: given that for sampling purposes, every second row in "Planned vs. realized" doesn't have active formulas - the formula in the "Overview" is set to refer to the date in the "Mat. Planned Starting Date". the real thing should refer to the date in "Prod. Real Starting Date"

I hope this clears it up a little now....

Looking forward to your reply,
Radu

Hi Radu

1) Do you want buttons on the sheet to insert the new orders on the Planned vs realized sheet? You could do it using change events, but it might lead to problems in changing existing data, inserting additional rows where you don't want them, etc.
2) Is it acceptable to have the orders on one line rather than spread over two? It would make the exercise a bit easier.

The following code answers part 2 of the problem:

Public Function FindSeries(TRange As Range, MatchWith As Single)
Dim cel As Range, x As String
    For Each cel In TRange
        If MatchWith >= cel.Value And MatchWith <= cel.Offset(0, 2).Value And cel.Value <> 0 Then
            x = x & cel.Offset(0, -2).Value & ", "
        End If
    Next cel
     
    FindSeries = Left(x, (Len(x) - 2))
     
End Function

Note the change in variable type for MatchWith from string to single.

Something else you might like to think about is Dynamic Named Ranges on the Planned vs realized sheet.

Cheers, Rob.

Hi, Rob

First of all - thank you! The code changes work like a charm!

Now - coming back to my first part (which I thought was the easy one! duh!):
1. Buttons are not necessary. they would probably be cooler - but then again - they might just make things harder to deal with.
2. I also suggested one line (mostly because I also thought I'd be easier) - but that was a "special request" from "the man in charge" So technically - I DO still need two rows. But if that's TOO complicated - just let it be; I'll convince him (easily!) that it can't be done
3. Dynamic Named Ranges mean nothing to me, at the moment; so I'd really appreciate it if you'd care to detail the matter a bit more.

Thanks a lot one more time!
(I'll keep the thread open until we clear the order issues, though)

Radu

One more thing: is there a way to split the result in Order Overview in different columns? Like one column for each active order?

Thanks

So I understand there are no new ideas here, right?
cause if that's true - I might as well close the thread....