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

CarolynD

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!

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.