Dec 09 2022 11:18 AM
Dec 09 2022 11:18 AM
Hey all! I hope I'm able to type this in a way where it makes sense.
For my job, I'm trying to fix up our list of interpreters to make it easier to navigate. There are currently three sheets: the first sheet is the main sheet where the search results would appear, the second sheet is just a list of the different languages people can search for, and the third sheet is all of the information of the interpreters. On the first sheet, I have a cell with a drop down menu filled with the languages from the second sheet, and using that menu people can search based on which language they need.
My issue is this: a lot of these interpreters speak multiple languages, so if someone searches for one particular language, an interpreter can show up on multiple lists. Currently, I have the third sheet set up as a table, with the name, phone number, email, and other notes as separate columns. To the side of this table I have every language each interpreter speaks, with one language in each column (so some interpreters have 5 or 6 columns after their row in the table).
How should I go about writing a formula so someone can select a language from that menu, and the interpreter's information will appear? I have columns set up on the first sheet for the information to generate into, but I just can't figure out how to get there. Should I put all of the languages in one column instead of multiple and separate them by commas, or are different columns okay? I can try to get a dummy list going if that will help everyone visualize
Dec 09 2022 02:42 PM
If I were doing this, I'd use one column for all languages and repeat the names and contact info of the interpreters on each row. That's because it's languages that are at the heart of what you're doing. Pick a language and I'll find you an interpreter.
If you were working in Access (a relational database) rather than Excel, it would make sense to have all the information on the person just once, with an ID, and repeat the IDs next to each language. But presumably your lists (of both languages and interpreters) are finite. This could be done in Excel too, but unless we're dealing with a really long list of names and languages, it might be overkill.
The other thing is to avail yourself of the UNIQUE and FILTER functions to deliver the results you're seeking. Here's a YouTube video, if you're not familiar with them. https://www.youtube.com/watch?v=9I9DtFOVPIg
And by all means, feel free to post a dummy copy of the sheets you're creating. I and others would be happy to show you how FILTER could deliver.
Dec 12 2022 06:36 AM
@meghan1728 attached is an example showing a sample 'data' set, pulling a UNIQUE set of all languages, Data validation for drop-down, and output table of matching results. I formatted the data as a table to make it easy to expand and contract as needed.
Dec 12 2022 12:16 PM
I've modified and built on the excellent start that @mtarler provided (I'm clearly lazier than he, since he put the effort in to actually create something!)..... just to show a different way to organize the table of Name and Language, resulting in slightly different, simpler UNIQUE and FILTER formulas to yield the same result--a list of names of interpreters who have the language skill sought.
For both of these solutions, please note that you will need Excel 2021 or newer, in order to use the UNIQUE and FILTER functions.
Dec 12 2022 01:11 PMSolution
@mathetes hey John, not to give you a hard time but I guess you are so lazy you forgot to attach your workbook. LOL. Sry I couldn't resist the poke. But seriously, I absolutely agree with you that another format would be much more efficient so updated my sheet with a recommended format. I'm curious how closely it will match your sheet.
Dec 12 2022 02:58 PM - edited Dec 12 2022 03:00 PM
You took it all to the next level. Great job!
The only "improvement" left would be to put the basic tables in the background (on a separate sheet) and have a "front page" where one selects the language desired and gets the resulting list.
Dec 12 2022 04:11 PM
Dec 12 2022 05:03 PM
Feb 23 2023 07:57 AM
Feb 23 2023 10:45 AM
Feb 28 2023 07:03 AM
This is what I fiered. I've a company managed computer, sa I can't force updates... This is why I provided the version# of my xlsx. Is it supposed to be ok ? What is the version@ of your xlsx ?
Feb 28 2023 08:38 AM
Mar 01 2023 12:36 AM