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



## Luke1690 (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

Try this formula in L17


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


----------



## Luke1690 (Dec 15, 2022)

Peter_SSs said:


> Try this formula in L17
> 
> 
> ```
> ...


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 (Dec 15, 2022)

```
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 (Dec 15, 2022)

Luke1690 said:


> no sorry it returned nothing.


Hmm, I wonder why?

Here is my sample data with that formula in L17

Luke1690.xlsmABCDEFGHIJKLMNO16PartQtyDescLoc17Part a1Desc 1Loc 118Part d2Desc 4Loc 419PartQtyDescLoc20Part a1Desc 1Loc 121Part bDesc 2Loc 222Part cDesc 3Loc 323Part d2Desc 4Loc 424Part eDesc 5Loc 525Part fDesc 6Loc 626Part gDesc 7Loc 727Sheet1Cell FormulasRangeFormulaL17:O18L17=FILTER(A20:D100,B20:B100>0,"")Dynamic array formulas.

What about trying this?

Start a fresh worksheet with nothing in it
Copy my mini sheet by clicking this icon at its top left: 


Select cell A16 in the fresh worksheet
Paste
Does that work as per my sheet above?




Luke1690 said:


> 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 (Dec 16, 2022)

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 (Dec 16, 2022)

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 (Dec 16, 2022)

please see the below.


----------



## Luke1690 (Dec 15, 2022)

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 (Dec 16, 2022)

Luke1690 said:


> please see the below.


Then are you sure that you actually are using MS 365 that your profile shows? FILTER is a standard function in Microsoft 365.


----------



## Luke1690 (Dec 16, 2022)

i didn't even know it was set to 365 im using 2016 i have changed it now. really sorry to waste your time.


----------



## Peter_SSs (Dec 16, 2022)

Luke1690 said:


> i didn't even know it was set to 365


It is not a default setting, so you must have specifically set it at some point. .



Luke1690 said:


> im using 2016


In that case try this formula, copied across and down.

Luke1690.xlsmABCDEFGHIJKLMNO16PartQtyDescLoc17Part a1Desc 1Loc 118Part d2Desc 4Loc 419PartQtyDescLoc    20Part a1Desc 1Loc 1    21Part bDesc 2Loc 2    22Part cDesc 3Loc 323Part d2Desc 4Loc 424Part eDesc 5Loc 525Part fDesc 6Loc 626Part gDesc 7Loc 727Sheet1Cell FormulasRangeFormulaL17:O21L17=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$20:A$100)/($B$20:$B$100>0),ROWS(L$17:L17))),"")


----------



## Luke1690 (Dec 16, 2022)

Thank you so much and thanks for being patient


----------



## Peter_SSs (Dec 16, 2022)

You're welcome. Glad we got there in the end.


----------

