Link multiple rows from source sheet based on filtered dropdown

beth_bojo

I have a contacts sheet listing 800+ vendors, speakers, & performers used for multiple events throughout the year at my organization - it's essentially a digital rolodex. Vendors table or present at one or more events each year, currently indicated in a dropdown list column that allows multiple selections.

I know I can create a report filtering vendors tagged by specific events, but I'd like to link the filtered data to an event sheet so additional columns can be added (added column needs differ from event to event), but the vendor data still only needs to be updated in one place if/as it changes. It's important to me to keep the source sheet of contacts updated in a central location, especially since some vendors table at multiple events.

Then I'll create a report for each event from the event sheet, eventually rolling all of these into a single dashboard for the year.

I have played with VLOOKUP & INDEX formulas, but can't get either to work. Is there a way to pull/link entire rows into a new sheet based on filtered data from a dropdown menu?

Thanks in advance!

Genevieve P.

Hi @beth_bojo

If you have access to the add-on Data Mesh, it can add and cell link rows for you across sheets. Here's more information: DataMesh

We could use an INDEX(MATCH or INDEX(COLLECT combination, but you would need some sort of identifier on each of your sheets so it knows what to match up. It sounds like in your case you just need to copy/paste one time, but have those cells linked back to the original sheet in case a row changes.

I know it's not automatic, but what about using Paste Special to create those cell links?

So, set up your filter to find your rows, then select the cells/rows you want linked, navigate within the same browser tab, and Paste Special to create the cell link. See: Create cell links, copy formula results, or formatting

Cheers,

Genevieve