Jan 31 2022 10:29 AM
Hey all,
I've created a sheet that has the following columns: (A) dates, (B) names, (C) phone numbers, (D) free text & (E) Status (done or closed).
The first row (frozen) contains the title of each column.
I've opened another sheet (same file) and called it 'Dashboard" . I am looking to create some sort of index where I can type in (search really) for a phone number and it will return the entire rows back with all the information. Same goes to searching for a name and return the entire relevant rows (with the phone number, dates and etc).
I've been trying to look for an answer online, and also tried by myself for 2 months .. and for the life of me, came back nothing but frustration.
Important to mention that I can't use macros & VBA as my workplace won't allow it due to policy issues. Needless to say, the information is sensitive.
Any ideas on how to do it and tackle it in the best way possible?
Big thank you in advance!
Jan 31 2022 11:14 AM
=FILTER('same file'!A2:E14,
IF(I2<>"",'same file'!C2:C14=Dashboard!I2,
IF(J2<>"",'same file'!B2:B14=Dashboard!J2)))
Maybe with FILTER function if you work with Office365 or 2021. If there is search criteria in cell I2 the data is filtered for a matching phone number otherwise for a matching name according to criteria in cell J2.
Jan 31 2022 11:28 AM
@addori attached is a sheet with an example of how to do a few things:
a) create data drop downs that auto filter based on partial entries (I.e. type "jo" to filter the dropdown to only show "john", "joseph", "joanne", etc..
b) to use then use a set of fields to return rows of a table that meet those inputs
The data in on 1 sheet and the lookup is on a second sheet with the 'helper' columns used to create the dynamic drop downs over on the right around columns M,N,O, etc... (you could hide these)
Feb 04 2022 11:01 AM
Feb 04 2022 01:23 PM