Hyperlink cell value connecting to a specific sheet row

ChittaChitta
edited 04/07/21 in Formulas and Functions
04/07/21 Edited 04/07/21
Accepted

Hi,

Would be really helpful if someone can suggest a solution to my below scenario:

  1. I have multiple projects in my program, I have a sheet which contains release names which is connected to task name project plan within program.
  2. I want to have a hyper link from my current cell to specific project task name. For example,

In my current sheet I have below release names


"CRM1.0 Production end user release training" is available in Project 1 and CLM2.0 UAT End user training is available in Project 2. How can find this specific "release name" present in a group of projects? VLOOKUP or MATCH can find in a specific project and I believe it cannot search in multiple projects as per my understanding.

Is it possible to have a hyperlink in cell "CRM1.0 Production end user release training" to connect to project 1? I want to get rid of manually linking to projects.

Objective of this exercise is to find a drill down tasks from each of these main tasks from specific project and fill up CHILDRENS in my current sheet.

Kind Regards,

Chitta

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    There is no way to link directly to a specific row, but you can add a column to include the row number on each sheet, then pull a report.


    The report will have a column that has a link to the sheet, and if you include the row number column from them, then the user will know exactly where to look after clicking on the sheet link.

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Chitta

    I'm always happy to help!

    The best option is to try to find something else that we could use to identify the parents if it's not an alternative to add a column (even if we hide it?).

    What do you think?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Is each project a different sheet?

  • Yes, Paul.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    There is no way to link directly to a specific row, but you can add a column to include the row number on each sheet, then pull a report.


    The report will have a column that has a link to the sheet, and if you include the row number column from them, then the user will know exactly where to look after clicking on the sheet link.

  • Thanks, Paul. I am going to try this approach to see if this helps. Thanks a lot for quick help.

    Kind Regards,

    Chitta

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️


    An option for quickly jumping to a row once you have opened the sheet is to press CTRL+G and then type in the row number and either hit "Enter" or click on "OK". That will help avoid a lot of scrolling.

  • Thank you, Paul 👍️

  • ChittaChitta
    edited 04/08/21

    Hi Paul,

    Does the newer version of Smartsheet report filter can accommodate formulas?

    I am finding difficulty on how to add formulas while I am building the report. Report filter only shows column names, condition and criteria but I want to use formula while fetching the data.

    Here is the screenshot I am referring:

    I want to retrieve parent child rows from my sheet but above condition will give only retrieve children rows but but not parent. I may be missing something here.

    Can you please suggest how to achieve this.

    Kind Regards,

    Chitta

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 04/08/21

    Hi @Chitta

    I hope you're well and safe!

    You can't use formulas in reports, but you can use the Grouping and Summarize features to create something similar.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • ChittaChitta
    edited 04/08/21

    Hi Andree,

    Thanks for letting me know that formulas don't work with reports. I was reading another thread using Parent, Children row retrieval and mention of formula and I was wondering if there was any option in older version which may have moved to different feature I may not know in newer version.

    Show both parent and child rows in report, even if the Parent doesn't meet the criteria — Smartsheet Community

    Considering Summarize and Grouping feature do you think my requirement can be achieved?

    I am reposting my earlier requirement shared in this post:  Is it possible to retrieve parent child rows in my report? I am filtering based on taskname = Training or Communication, I want to retrieve parent of applied conditional row.

    Or do you suggest any other approach?

    Kind Regards,

    Chitta

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Chitta

    Happy to help!

    Yes, you could so-called helper columns that indicate if it's a parent, the parent's name, and then we'd use that together with the grouping feature.

    Another option that I often use in my client solution is to add a so-called helper column showing the parent name or hierarchy on each row to see it but don't necessarily need to use the grouping feature.

    What do you think?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi Andree,

    Helper column is a default column in sheet? if not, adding a column project template is not an option as project templates are used by at least 12 projects so adding another column in project template requires approval from teams and most likely to get rejected.

    Wondering if there any other way of achieving this requirement without disturbing project sheet.

    I really appreciate the solutions you and Paul is sharing, I am learning a lot!

    Kind Regards,

    Chitta

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Chitta

    I'm always happy to help!

    The best option is to try to find something else that we could use to identify the parents if it's not an alternative to add a column (even if we hide it?).

    What do you think?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thank you, Andree :-)

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Chitta

    You're more than welcome!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.