Search Engine

tly0227

New Member
Joined
Oct 2, 2012
Messages
37
So I have an Excel file with 2 sheets on it. Sheet A (Organizations) is a list of organizations with their info (address, phone, website, etc.). Sheet B (Contacts) is a list of people with their info (name, title, organization, email, etc, and fields that detail when they were contacted, by whom, etc.). On Sheet A, a specific organization is only going to have 1 row total. On the contacts sheet, it's quite possible that there is more than 1 contact for a specific organization, and there is only 1 row per contact. Both lists are extensive.

What I want to do is this:

Have a new sheet with a search box that will search rows/columns from both the organizations and contacts list and display those results.

I know I can figure out how to do that but I want to take it a step farther and having those results displayed in a way that is editable. So say for example I search for 'XYZ School'. I want to see all the fields for that org from the organizations sheet and then all the fields for any of the contacts I have from that org...and then be able to edit them right there on that sheet and for those edits to save on their respective tablets on the orgs and contacts sheets.

I know enough that I can figure out how to do this if its possible. So my questions are really 1) is it possible. 2) is it called something specific that I should be Googling to find instructions?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hello,

have assumed the following:

Organizations sheet has the organization name in Col A.
Contacts sheet has the organization name in Col C.

The sheet you want the data pulled into is called ALL_DATA.

Code:
Sub GET_DETAILS()
    Application.ScreenUpdating = False
    MY_ORG = Sheets("ALL_DATA").Range("A1").Value
    Sheets("Organizations").Select
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=MY_ORG
    Rows(1).Offset(1, 0).Copy
    Sheets("ALL_DATA").Range("A3").PasteSpecial (xlPasteValues)
    Selection.AutoFilter
    Sheets("Contacts").Select
    Range("A1:C1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:=MY_ORG
    Rows("2:" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Sheets("ALL_DATA").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    Selection.AutoFilter
    Application.ScreenUpdating = True
End Sub

You enter the organization into A1 on ALL_DATA tab and run the macro.

Changing the data on the ALL_DATA tab then to be updated onto the other sheets, will be quite tricky.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,203,485
Messages
6,055,685
Members
444,807
Latest member
RustyExcel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top