Auto populate cell based on contents in two other cells in the same sheet

Hello - I've created a intake form to populate a sheet senior management will use for their assigned markets. Management has requested a link in the sheet to a report based on the contents in "Action Plan" (Yes) and "Market" (dropdown with 40+ city names). The manager would use the link to view the report without having to open a second window and search for the report.

Here is a screen shot of the sheet. In the MRG Link column, I manually added the link to the action plan report.

How can this be accomplished?

Thanks for any assistance!

Find more posts tagged with

Sheets

Reports

Automations

Accepted answers

All comments

Mark.poole

it can be accomplished. First you would create a reference sheet that’s has all the markets and their associated links next to them. Once that’s done you do an if formula with an index match in it. I will gladly build the formula once you get the reference sheet set up. For now you can base it off this.

=IF([SS Action Plan]@row =“Yes”, Index({Report Link Ref}, Match(Market@row,{Market Ref},0)))

Mark.poole

After further research you can’t actually drive a hyperlink with a formula. While my idea will pull the text it will not be a hyperlink. I am sorry I did not know this before my original post. It does appear though it will work if you use the full url instead.

CarolynD

Thanks, Mark. I appreciate your input and verifying how it will work. I'll give it a try to see how it will work.

CarolynD

Mark - I created the reference sheet (Ref Link Sheet) with the columns "Market" and "Link" (which is the URL to the action plan.

The formula you provided is a bit advanced for me and I'm not able to get it to work. The "Market" is linked to the formula in the "Perf Update Notes" sheet so I know this part is working. Could you expand on the Index and match functions? Thanks!

Mark.poole

@CarolynD

Carolyn. I apologize for the amount of time it took to respond. That's part of the reason i always tag who I am speaking to so they get alerted when I respond. as for the formula.

First right click on the sheet you are trying to pull the information to and select Manage references. Create a reference to for the Market column in your reference sheet. I would recommend changing the name to something easier to type for the formula. Like naming the ref Market. Then create you one for the Link Column. Again i would recommend naming this ref Link.

doing this the formula should look like this if you used the recommended names for your refrences.

=IF([SS Action Plan]@row =“Yes”,INDEX({Link}, MATCH(Market@row,{Market},0)))

IF your still having an issue with it. I am more then willing to hop on teams to help you out. I am bookmarking this conversation so i can better track it for you until we get the solution for you.

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.