Is this even possible...???

[csroberts]

n00b here, LOL, I need help in a certain formula or whatever, to like, ya know...

Ok... *breathes deeply* ... so like...

Here, let me explain what I am looking for...

I am looking for a formula that shows results in Cells(A2:D2) from a range in another sheet (A2:D7) based on whats being typed in Cell(A1).

So like, for example in pictures...

I would like Test1, A2:D2 to show results from Test2, A2:D7 range corresponding to what I type in A1.

So lets say I am typing Blue in A3, I would like A4 to display a selection of the two blue yarns I have in stock, then upon selecting the second blue yarn ($3.43) A4 to D4 shows the results from Test2 A6 to D6.

Or even if I typed red like in A1, I want A2 to D2 to display results from Test2 A2 to D2 even without multiple selections like asking for as above this sentence.

I hope this makes sense in my question. I am looking for a search-type function that shows suggested results on one sheet from a second sheet - if this is even possible.

I love using excel, so this will probably be the start to a few questions, if anyone can please help or send me to a thread in this forum, or even anther method that gets similar results - that would be fantastic.

Thanks.

 

[Rick Rothstein]

It looks like you have merged cells on the Test2 sheet. Are only cells B1:C1 merged with the 100's in Column B and the "ft" in Column C from from Row 2 downward? Or are the cells in Columns B:C merged row by row for the entire column?

 

[Rick Rothstein]

I am going to assume the cells on the Text2 sheet are not merged from Row 2 on downward. With that assumption, I think you can use this Change event code to do what you want. Note, though, that it will find any text you enter even if not a complete word. So, if you type "poo" in a cell in Column A on the Test1 sheet, it will retrieve the data for all the cells in Column A on the Test2 sheet that contain "poo" which, for your example data, is the single "Poopie Yarn" row. The code also assumes you are at the blank row below your retrieved data on the Test1 sheet (otherwise it will blindly overwrite any data on the row or rows below your entry depending on how many rows of data it found with your search word. Okay, with all those provisos in mind, here is the code...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Long, Data As Variant, SearchMe As Variant, Result As Variant
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Column = 1 Then
    With Sheets("Test2")
      Data = .Range("A2", .Cells(Rows.Count, "D").End(xlUp))
    End With
    ReDim SearchMe(1 To UBound(Data))
    For R = 1 To UBound(Data)
      SearchMe(R) = Join(Application.Index(Data, R, Split("1 2 3 4")), vbTab)
    Next
    Result = Filter(SearchMe, Target.Value, True, vbTextCompare)
    If UBound(Result) > -1 Then
      Application.EnableEvents = False
      Application.ScreenUpdating = False
      With Target.Offset(1).Resize(UBound(Result) + 1)
        .Value = Application.Transpose(Result)
        .TextToColumns , xlDelimited, , , True, False, False, False, False
      End With
      Application.ScreenUpdating = True
      Application.EnableEvents = True
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code (your Test1 sheet) and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[csroberts]

It looks like you have merged cells on the Test2 sheet. Are only cells B1:C1 merged with the 100's in Column B and the "ft" in Column C from from Row 2 downward? Or are the cells in Columns B:C merged row by row for the entire column?

They aren't merged, when cells are colored the same the line between disappears until border is applied.

 

[Rick Rothstein]

Did you try the code I posted in Message #3 yet?