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

Options

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!

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24
    Options

    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)))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24
    Options

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • CarolynD
    Options

    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
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

    @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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!