Is it possible to have users use a form to search for a line item and auto send an update request?

Murph
Murph ✭✭
edited 10/17/22 in Formulas and Functions

I have a SmartSheet that lists contracts with fields that include contract end date, purpose of contract, vendor contacts, etc. This information needs to be updated by the manager who is responsible for the contract, but I don't know which of our managers are responsible for which contracts to update. Is there any way to create a form that I can send out to all managers that lists all the contract line items in the SmartSheet in a drop down list that they can select and also asks for the manager to enter their email address? This would update the specified contract line item with the responsible manager's email. Then, an automation would run to send out a Request for Update to the email provided on the form for the manager to update the remaining fields for that contract line item on the Smartsheet. I don't want to share the sheet with all the managers as some information is confidential.

Answers

  • Hi @Murph

    What I would do here is actually set up a second sheet to share out. This sheet would only have the Contract ID or Name (the way to identify it without sharing sensitive data), and a column for the Contact / email associated.

    This way you can share the entire sheet with the Managers and ask them to fill out the appropriate contacts.

    Then, in your source sheet, you could either cell-link the contacts into your private contact column, or use a formula (matching the ID across sheets) to bring back that contact. If you use a formula, make sure that your second, shared sheet has the exact same data from your original sheet in the Contract identifier column so it can find a matching row.

    An easy way to do this may be to save a duplicate copy of your current sheet with Save as New, then delete out all unnecessary columns. This will ensure your Contract data is identical across sheets!

    See: Cell Links: Consolidate or Maintain Consistency of Data or Formula combinations for cross sheet references

    Let me know if that makes sense or if you need help with the formula!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!