link between sheets

Paula ZM

Hi!

I'm working with two different sheets. The first one has all marketing activities (webinars, events, trade show, etc). The second one has all the marketing assets created (Ebook, social media cards, etc).

I would like to link which assets can be used for which marketing activities. A marketing activity can use more than one asset. An asset can be used for various marketing activities.

I would like to have both perspectives:

a specific marketing activity is using which assets?

a specific asset is being used to which marketing activities?

Is there a way to do it?

Thank you!

KPH

Hi @Paula ZM

You could add a multi-select drop-down list to your activities sheet with a list of all available assets. At the moment, the drop-down list can't be automatically populated from data in your assets sheet, so you would need to manually add each new asset to the drop-down when they become available*.

On the assets sheet, you could use a cross sheet formula to list all the activities that use that particular asset. A combination of JOIN, COLLECT, and HAS could work. I'd also throw in an IF, NOT, and ISBLANK for error handling and a CHAR(10) to put each event on a new line.

Worked example:

Event sheet with drop-down list

Assets sheet with formula filling in the "Used at" column

Formula to copy/paste

=IF(NOT(ISBLANK(Asset@row)), JOIN(COLLECT({Events Name}, {Assets}, HAS(@cell, Asset@row)), CHAR(10)), " ")

Cross-sheet references

If you copy and paste the formula as is you will need to change the column heading Asset to match the one in your file, and also will need to set up the cross-sheet references. If you highlight the parts shown in the curly braces a helper box will pop up. Click the link to reference another sheet. Select your Assets sheet from the tree. Highlight the appropriate column and give it a name. That name will then appear in the curly braces in your formula.

In the example, I did this:

* If you create assets more often than you create activities you might want to swap this process around and put the drop-down on the other sheet.