how do I export specific data from a smartsheets sheet to a preformatted excel file

I have a smartsheet file with pricing information by part number. I have a sales quote sheet in excel/google sheets. I want to have the sales staff type in the part number on the quote sheet and pull the pricing and description from the parts file in smartsheets. What is the best way to do this? Is it data shuttle?

Thank you!

Answers

  • Will Jeffords
    Will Jeffords Overachievers

    Hi @heidifoster , yes - Data Shuttle is your friend! I would be happy to walk through your scenario live if you want, just let me know, but also given your description - you may want to use Dynamic View or an automation to help you with the sales staff workflow that you describe…I'm thinking a Dynamic View filtered to "current user" for each sales staff that would allow them to enter part numbers and that would then result in your Data Shuttle pulling what you need into the pre-formatted Excel. Of course there is always the question - why is the sales quote sheet in Excel/Google versus Smartsheet? We are all dying to chip away at that issue too! :)

    All my best,

    Will

  • heidifoster
    heidifoster ✭✭✭

    Hello Will and happy Friday. I would love some help with this as I have a trial of all the advanced options right now and need to decide if they will work for us. So currently our sales staff fills out an excel spreadsheet which has our quoting info on it as well as a word doc that the customer gets. As this is a manual process it leaves a lot of room for error. So I am trying to create something in smartsheets that will feed the excel quote sheet or create a new one using data on pricing and parts in a sheet I build that can then be updated by our parts department when changes occur. I would like it to be a form that feeds the file to make it easy on the sales guys but not sure if that is possible. Each quote could have multiple line items of parts (some up to 50-75). I have attached a blank copy for you to review. This is above my skill level so any help is greatly appreciated.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 05/19/24

    Hi,

    I hope you're well and safe!

    This seems to be convertible to a Smartsheet instead, and then we could develop the optimal structure for populating it with a form.

    Is that an option, or does it have to be in Excel?

    Also, I agree with Will that Data Shuttle could work for this; otherwise, there might be a 3rd-party service we could use instead.

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

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

    Did my comment help or answer your question? Please support the Community and me by marking it
    💡 ⬆️ ❤️ or/and as the accepted answer. It will make it easier to find a solution! Thanks!

    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.

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    Agree with Andree and Will (as usual!) - unless you absolutely MUST maintain that Excel file, it's going to be considerably easier and more stable for everyone to move it and have people fill it in inside Smartsheet. Then you're just doing lookups and moving data around inside 1 tool instead of 2.

    Plus Smartsheet has the concept of hierarchical rows that you can indent/child underneath other rows to many levels, which sounds perfect for what you're doing here with multiple line items for a given quote.

    Data Shuttle can export your Smartsheet data and match to Excel rows based on unique identifiers, but the catch is that any DS workflow points to a single file. It looks to me like you're going to have lots of Excel files out there, each one needing population. You will need to copy your DS workflow for each destination quote. Maybe that's manageable, maybe not..

    Inside Smartsheet it's very manageable, you just setup the template one time with formulas that point to your entry sheets and pull in the data using INDEX/MATCH or COLLECT type functions, and then copy that template as many times as you need to. You can also leverage Control Center to automatically generate new templates when new quote requests are received and setup all the data connections for you.

    All that said, it's relatively advanced setup that needs a solution build. That's a little beyond the scope of a community post. If you don't have a Smartsheet builder to lean on at your company then I know Andree and some others can provide consulting services to help you out. You can also ping your Smartsheet account rep, they have resources typically that they can bring to the table to help you design a solution, and Pro Services support to build for you.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN