Validation List choice to copy row from another sheet

[philobr]

Hi there,
I have two similar problems.
The first is that I need to get data copied from sheet1 to sheet2
depending on selection in a validation list.
The data is on the same row and all the same range.
So it would look something like this.
A B C
Fruit Orange Apple Pear
Veg Potato Carrot Onion
Animal Bear Cow Dog

I have the data in A in a dynamic range and validation list.
I need all the data copied from the specific rows ie
choose Animal from the validation list in Sheet2!A1 and
the entire row Bear Cow Dog get copied to Sheet2!B1, Sheet2!C1, Sheet2!D1.

The second problem I think is a lot more complex.
So the same scenario above but this time its a multi select on Sheet3.
I need to be able to select two (or more depending on Fruit and Animals to display in A1, A2 and
then their valid options to appear in B, C and D

I've looked through the board and there doesnt seem to be anything like this so any help will be greatly appreciated.
Thanks

 

[xlHammer]

Ok this might help you a bit more,

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim x1 As Double, x2 As Double
Dim R1 As String
 
If Target.Address = "$B$3" Then
 
x1 = WorksheetFunction.Match(Target.Value, Sheets("Sheet2").Range("A:A"), 0)
Sheets("Sheet2").Rows(x1).Resize(1, Rows(x1).Columns.Count - 1).Offset(0, 1).Copy
 
Do
    x2 = x2 + 1
    R1 = Range("A9").Offset(x2, 0).Value
Loop Until R1 = ""
 
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A9").Offset(x2, 0)
 
End If
 
End Sub

This pastes the results in the next available row each time you make a selection, so you can view more than one at a time. Any good?

 

[mikerickson]

Draging that formula to all cells of the destination row should fill them with the appropriate values.