Is this even possible...???

csroberts

New Member
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.

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Rick Rothstein

MrExcel MVP
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

MrExcel MVP
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...
VBA 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

New Member
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

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

Replies
4
Views
51
Replies
1
Views
45
Replies
2
Views
48
Replies
3
Views
137
Replies
1
Views
54

1,128,074
Messages
5,628,484
Members
416,321
Latest member
tomazik123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back