cross sheet data - input

Options

Am reading so many topics I'm now lost so reaching out.

Our 'Sales Order Activate' sheet is fed by a form, if the [Permit] checkbox is ticked I want our new Permit sheet to recognise that tick and populate a cell in the Permit sheet with the sales order number

=IF(INDEX({Sales Order Activation Range 7}, 1) = true, INDEX({Sales Order Activation Range 4}, 1), " ")

In this formula I believe 1 is the row, I would like to use @row but on doing that formula was unpassable?


Thanks in advance

Cheers.

Forever forwards Backwards never.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    To do it with formulas, you would need to "pre-populate" multiple rows on the destination sheet with the formulas, and we would end up needing to add a couple of columns to both sheets.


    With the copy row automation, you don't need to "pre-populate" anything on the destination sheet and can hide the extra columns so they are not visible. Automations running on the destination sheet can be set to only display selected columns in the email notification.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Jason P

    I hope you're well and safe!

    Fortunately, that is not the case. Any hidden column will stay hidden.

    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 EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Jason P

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Have you looked into the copy row automation? What is the reason for copying this number over to another sheet?

  • Jason P
    Jason P ✭✭✭✭
    Options

    Morning Paul

    I have and use that feature in other sheets however for this one there are 34 columns in the Sales sheet and not all data is require in my permit sheet (hence my @row). Ultimately I'm looking to pull 6 non consecutive cell data from the Sales sheet placed input to the new Permit sheet. The formula below is just one cells data I'm looking to pull - I figure I would use the same for the other 5 cells I'm looking to pull.

    Once working it will alert our permit support team of the request, provide only relevant data and start automations. I played with the formula last night

    =IF(INDEX({Sales Order Activation Range 7}, @row) = true, INDEX({Sales Order Activation Range 4}, @row), " ") but I get #unparseable

    If I replace @row in the above with a 1 or 2 it only picks up on that row number from the sales sheet and only if the box is ticked (so I'm 50% there). I feel I'm close, if I can get the @ Row to work I should only get data from the Sales sheet into the Permit only when the permit check box is ticked... hope that makes sense.

    Cheers.

    Cheers.

    Forever forwards Backwards never.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    To do it with formulas, you would need to "pre-populate" multiple rows on the destination sheet with the formulas, and we would end up needing to add a couple of columns to both sheets.


    With the copy row automation, you don't need to "pre-populate" anything on the destination sheet and can hide the extra columns so they are not visible. Automations running on the destination sheet can be set to only display selected columns in the email notification.

  • Jason P
    Jason P ✭✭✭✭
    Options

    Thanks Paul, I'll give that a try first.

    My understanding of the move / copy row feature is that any hidden column on the destination sheet will be unhidden when a new row comes across? if so this would make the destination sheet very long and cause confusion at the users end having to hide columns every time a new entry came across, this was the main reason for a formula based solution.

    Cheers.

    Forever forwards Backwards never.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Jason P

    I hope you're well and safe!

    Fortunately, that is not the case. Any hidden column will stay hidden.

    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 EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jason P
    Jason P ✭✭✭✭
    Options

    Hi Andre, Paul @Paul Newcome

    You guy's are great, I really appreciate you all taking the time to answer user questions and always with a sense of urgency, I enjoy reading the other solutions you contribute to also.

    I have set up the automation of 'copy row' and discovered as you say columns stay hidden, this will help me too in other sheets; brilliant.


    Thanks again, until next time.

    Cheers.

    Forever forwards Backwards never.

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

    @Jason P

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!