displaying data in different cells, Depending on the value of 1 cell.

[Luke1690]

Morning,

i have a a part list, where people can select QTY of parts they require.

When the QTY is chosen i need the data displayed in different cells

qty Range B22:B55 Amount that they can order is 1 or 2

When a 1 or 2 is selected in the qty box (B22), i would like cellls A22,B22,C22,D22 to show in cells L9,M9,N9,O9

If a another cell has a qty selected lets say (B24), Cells A24,B24,C24,D24 To show in cells L10,M10,N10,O10

and so on.....

Is this possible?

ive been looking for videos to train my self but not to sure where to look, if someone can point in the right direction.

Thanks guys

 

[Peter_SSs]

Perhaps you could give us a small set of dummy sample data and the expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Luke1690]

Apologies i couldn't download the add on. i have however sniped a picture of a rough diagram.

so as you can i see i have selected QTY 1 in cell B20, I would like range A20:D20 to show in range L17: O17

Then if more qtys are chosen for example cell B23 on the diagram, range A23:D23 is shown below the first order, in range L18:O18

 

[Peter_SSs]

Try this formula in L17

=FILTER(A20:D100,B20:B100>0,"")

 

[Luke1690]

Try this formula in L17

=FILTER(A20:D100,B20:B100>0,"")

no sorry it returned nothing.

So im trying to do this my self
i have the following macro
Range("A17:D17").Copy Range("L4:O4")
which when ran copys the cells i need to the correct location

i just need it only run IF, 1 or 2 is entered in the qty cell range B17:B50

 

[Luke1690]

Sub COPY1()
'Use the Range.Copy method for a simple copy/paste

    'The Range.Copy Method - Copy & Paste with 1 line
    Range("A17:D17").Copy Range("L4:O4")
  
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) And Target.Address = "$B$17" Then
        Select Case Target.Value
        Case 1 To 2: COPY1
     
        End Select
    End If
End Sub

So this works perfect for 1 line how can i change it so if the value is changed to a 1 or 2 in range B17:B50
IT copys that line instead of just A17:D17 as code states above

 

[Peter_SSs]

no sorry it returned nothing.

Hmm, I wonder why?

Here is my sample data with that formula in L17

Luke1690.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
16												Part	Qty	Desc	Loc
17												Part a	1	Desc 1	Loc 1
18												Part d	2	Desc 4	Loc 4
19	Part	Qty	Desc	Loc
20	Part a	1	Desc 1	Loc 1
21	Part b		Desc 2	Loc 2
22	Part c		Desc 3	Loc 3
23	Part d	2	Desc 4	Loc 4
24	Part e		Desc 5	Loc 5
25	Part f		Desc 6	Loc 6
26	Part g		Desc 7	Loc 7
27
Sheet1

Cell Formulas
Range	Formula
L17:O18	L17	=FILTER(A20:D100,B20:B100>0,"")
Dynamic array formulas.

What about trying this?

1. Start a fresh worksheet with nothing in it
2. Copy my mini sheet by clicking this icon at its top left:
   
3. Select cell A16 in the fresh worksheet
4. Paste

Does that work as per my sheet above?

Apologies i couldn't download the add on.

Is that because of workplace security restrictions or did you just have other difficulties?
As another option to give us some you your actual sample data, you can just directly copy a small sample section from your worksheet and paste in your forum post and tell us what cells you have copied from.

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

 

[Luke1690]

Security Restrictions unfortunately.

i followed your steps and it returns the following.

its probably something simple I'm doing wrong, I'm not an expert.
That formula would be so much easier for me.

The vba code i created works fine its just so long winded it might be easier to shorten that for me if possible. i have created a private sub for each cell that if a 1 or 2 is put in the cell it runs a vba code of my choosing and copys the files and pastes them in the range L17:O17. i have had to create the private sub for each cell and then to corresponding sub code to go with it.

Sub COPY1()

Set wsCopy = Workbooks("########").Worksheets("########### ")
Set wsDest = Workbooks("##########r.XLSM").Worksheets("######### ")
wsCopy.Range("A49:D49").Copy
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "l").End(xlUp).Offset(1).Row
wsDest.Range("l" & lDestLastRow).PasteSpecial Paste:=xlPasteValues

End Sub
Sub COPY2()

Set wsCopy = Workbooks("#########").Worksheets("####### ")
Set wsDest = Workbooks("############").Worksheets("###### ")
wsCopy.Range("A50:D50").Copy
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "l").End(xlUp).Offset(1).Row
wsDest.Range("l" & lDestLastRow).PasteSpecial Paste:=xlPasteValues

End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$B$17" Then
Select Case Target.Value
Case 1 To 2: COPY1
End Select
End If
If IsNumeric(Target) And Target.Address = "$B$18" Then
Select Case Target.Value
Case 1 To 2: COPY2
End Select
End If
If IsNumeric(Target) And Target.Address = "$B$19" Then
Select Case Target.Value
Case 1 To 2: COPY3
End Select

 

[Peter_SSs]

The "That function isn't valid" error message seems very strange to me. In a vacant cell if you type =FIL do you see the FILTER option like this?

 

[Luke1690]

please see the below.