How to make multi-select selections be clickable links (in a report or dashboard)

Hi there,

Is it possible to add hyperlinks to each option in a multi-select column? For me this is less important in a Sheet than in a Report (or Dashboard), but I presume this would need to be set up in a Sheet for it to flow to a Report or Dashboard.

To elaborate, I'd like a Report to have each selected item in a multi-select column be a link to another document. I don't actually care if the items in the Sheet are clickable, but when generating and publishing a Report, I would like the items to be clickable.

Ideally this would be as easy as adding links to the items in the multi-select column properties' Values -- i.e., highlight each line, hit ctrl-k, add URL, done. This does not currently appear to be possible.

I imagine, though, that this would take some formula wizardry... Perhaps:

Add a column that would concatenate the selected multi-select column's values (is this possible?),
Add a URL to each of them (is this possible? I don't see any URL() or LINK() or HYPERLINK() functions)...
...based on an IF() function to apply the correct URL depending on which multi-select item was selected.

Is this possible? If so, any suggestions?

Find more posts tagged with

Formulas

Government

hyperlink

Accepted answers

All comments

Andrée Starå

Hi @Jamie Bedford

I'll begin with the same answer, but there is a possible workaround this time.

😉

Unfortunately, it's not possible at the moment to add the link to an option in a multi-select column, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could either

add the clickable links to the multi-select column (could be shortened links)
add some helper columns that would join (concatenate) the different options from the multi-select column and show them as links

How many options can you have in the multi-select column?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

What do you think?

Would any of those options help/work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jamie Bedford

The sheet that I'm working on currently is basically a catalog of purchasable items -- currently about 70 items but likely to grow over time. Each item has one or more documented procedures associated with it that describe how to make a purchase request of that item.

Many of the items can be requested through the same procedures. Some items can be requested through multiple procedures. i.e., A laptop can be requested either through an "employee onboarding procedure," or if requested later in their tenure, could be requested through a "technology request procedure."

There are or will be up to 10 or so documented procedures to choose from. These documented procedures live elsewhere (on our SharePoint Online instance as Word docs).

In the multi-select column, we will select each relevant procedure for each item. Most will have only one or two selected procedures, but some will have more. Depends on the item.

The catalog itself is quite complex, so we will publish a report that limits the columns/rows to only what is relevant for each audience or circumstance (e.g., onboarding vs mid-term, or "laptop package" vs "desktop package").

I don't know whether your first workaround suggestion would work for our case -- I'm not sure about using a URL shortener, and I think this would be confusing for folks.

Your second suggestion sounds more doable, though!

Could you elaborate on how to show concatenated values as links? Is there a way to force such an encoding?
I'm comfortable with logic functions and concatenation functions.
I'm not sure how to turn text into a clickable URL, though.
I'm also not sure how to evaluate a multi-select for each selected item... Would this be a CONTAINS() kind of function? Is the multi-select just stored as a CSV style, or as a key-value pair style?
For a multi-select that has two selections, how would I put a different URL for each of the selected items?

(This isn't a huge rush for us, so it's unlikely I'll get to create a sample sheet for a bit, but I'll try to get back to that.)

(Also, I'll submit a feature request ASAP -- thanks for sharing that link!)

Thanks much!

Jamie

Jamie Bedford

I just tested a few options, and it seems that neither concatenation w/ a "+" nor JOIN() retain hyperlinks from referred cells. SUBSTITUTE() also removes links from referred cells.

In other words, if I have helper cells with the links that I want, and then I use a formula to concatenate whichever of those cells I want (i.e., by using a logical function like IF() or CONTAINS()), the result of those concatenations is plain text without the link.

I think that means that my desired functionality is not actually possible currently. Bummer.

(I'm on gov't instance, btw -- not sure if functionality is different for commercial tenants.)

Andrée Starå

@Jamie Bedford

Happy to help!

It should work!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.