Using a hyperlinked cell in a formula

Options

I have a master sheet that pulls data from many other sheets. Each row feeds from another sheet and there is a column that specifically contains the hyperlink to the sheet that row is referencing. Is there a way to write a formula for an entire column to use the hyperlink to pull data from the other sheets? Example below. The "BU" is located in the same place on all of the individual sheets so the formula seems like it should work. I am currently going line by line and "linking cell to a cell in another sheet". I would just like to save some time by writing a column formula so as we add Project Plan rows, it auto feeds. Thank you for any assistance you can provide.


Answers

  • Jaykel Torres
    Jaykel Torres Employee
    edited 04/11/21
    Options

    Hey @Chad Kirkpatrick,

    Great question! By using the VLOOKUP Function and Cross Sheet Referencing, you can pull in a hyperlink from another Sheet. Assuming that your "BU" Column is unique, you may use this as your "search_value" within your VLOOKUP Formula and return the corresponding hyperlink.

    In reviewing the image provided, I did notice that you have hyperlinked text values; any Cell Reference or Cross Sheet Reference will return hyperlinked text as text values only. In order for it to return a hyperlink, this will need to be the website URL (for example, use "https://smartsheet.com" instead of "Smartsheet")

    I hope this helps!

    Jaykel

  • Chad Kirkpatrick
    Options

    Jaykel,

    thanks for the response. Unfortunately, I don’t think I phrase my question correctly. I would like to use the URL in the Project Plan Link column to return the BU which is a value in each separate project plan that the link takes you to. I would like to add a column formula so that as new project plans are added, it would automatically go out to that project plan through the link and return the BU value. I am currently using cell linking to pull the values one by one as a project plan is added. I was just wondering if there was a more efficient way of doing this through a column formula. Does that better explain what my original question was trying to ask? Thank you in advance for any guidance!

  • Carsonelli
    Carsonelli ✭✭✭
    Options

    I too am curious about this functionality. Is this possible with a function?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!