Extract Values From Cells Based On Corresponding Cell Value

[fiberboysa]

Hi Dear Allz,
I am trying to extract values from preceding rows based on a value which is given in next columns.

Like in following example, in cell A2 Cell value is 10 (Highlighted in Green) the corresponding value in cell D2 is 3 (Highlighted in Red) so the value required in Cell E2 is combination of following three rows of Cell A2 i.e. values of cells A3, A4 and A5 which are A-1111 B-2222 C-3333 respectively. And in case of Cell A6 the corresponding value in cell D6 is 2 so the required value in cell E6 will be D-4444 E-5555.

The data continuous like this and formula or VBA code will fill my requirement in column E. Kindly let me know if you need further clarification. Thanks in advance...

	A	B	C	D	E
1	CLMN 1	CLMN 2	CLMN 3	CLMN 4	CLMN 5 (Required Formula/VBA)
2	10			3	A-1111 B-2222 C-3333
3	A-1111
4	B-2222
5	C-3333
6	20			2	4444 5555
7	4444
8	5555
9	30			4
10	F-6666				F-6666 7777 H-8888 9999
11	7777
12	H-8888
13	9999

<tbody>
</tbody>

 

[Fluff]

You're welcome & thanks for the feedback

 

[fiberboysa]

Dear Fluff,
There is one more request...
What if I want SLR# in the next line instead of seprated by a space? Like this...

	A	B	C	D	E
1	CLMN 1	CLMN 2	CLMN 3	CLMN 4	CLMN 5 (Required Formula/VBA)
2	10			3	A-1111
3	SLR# A-1111				B-2222
4	SLR# B-2222				C-3333
5	SLR# C-3333
6	20			2	4444
7	SLR# 4444				5555
8	SLR# 5555
9	30			4
10	SLR# F-6666				F-6666
11	SLR# 7777				7777
12					H-8888
13	SLR# H-8888				9999
14	ABCXYZ 12
15	SLR# 9999

<tbody>
</tbody>

I have tried the following but its not working...

Function fiberboysa(Rng As Range, Rws As Long) As String
   Dim i As Long
   Dim Cl As Range
   
   For Each Cl In Rng
      If Left(Cl, 4) = "SLR#" Then
         fiberboysa = fiberboysa & Cl.Value
         ActiveCell.Offset(1, 0).Select
         i = i + 1
         If i = Rws Then Exit For
      End If
   Next Cl
   fiberboysa = Trim(Replace(fiberboysa, "SLR#", ""))
End Function

Function fiberboysa(Rng As Range, Rws As Long) As String
Dim i As Long
Dim Cl As Range

For Each Cl In Rng
If Left(Cl, 4) = "SLR#" Then
fiberboysa = fiberboysa & Cl.Value
Application.SendKeys "{ENTER}"
i = i + 1
If i = Rws Then Exit For
End If
Next Cl
fiberboysa = Trim(Replace(fiberboysa, "SLR#", ""))
End Function

 

[Fluff]

You would be better of with a formula for that.
As that is beyond my knowledge of formulae, you had better start a new thread.