How to connect sheet to multiple datatables

I need to consolidate information from multiple excel sheets (over 100k lines per excel) via datatables to one sheet. Can you please help me how to achieve that? You can have only one connection to sheet, which I don't understand. I can't continue using Smartsheet without this function. Thanks for help and support

Tags:

Answers

  • Hi @Petr Boublík,

    To consolidate data from multiple Excel sheets into Smartsheet, you can try Data Shuttle. It allows you to automate imports from Excel files, but since each sheet can only connect to one data source at a time, you'll need to set up multiple Data Shuttle workflows.

    An alternative is combining your Excel data into a master Excel file or SQL database first, then linking it to Smartsheet through Data Shuttle or API. This way, you can still manage large datasets efficiently.

    If you'd like help setting this up, feel free to reach out.

    Best,

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • Lindsay Whitbread
    Lindsay Whitbread ✭✭✭✭✭

    Keep in mind that with DataTable you're still constrained by the 20K row limit of Smartsheet. Using the filter feature of Data Shuttle should also allow you to connect to a large Excel sheet and then send the rows you're interested in to the sheet.

    Smartsheet Lead @ InfoSpark

    Asia Pacific Smartsheet Partner of the Year (2023)

    Platinum Smartsheet Partner | www.infospark.com.au

  • Thanks all for answers. I will explain my solution. I have sheet with items which we produce (hundreds). Then I have 2 different dataTables. I want to read data from dataTables via VLOOKUP function, connected via item number. I don't want to copy all data from dataTables to sheet. I just want to use VLOOKUP in sheet.

  • Hi @Petr Boublík,

    Smartsheet doesn’t support live VLOOKUPs across DataTables. One option is to use Data Shuttle to pull only relevant rows based on your item numbers. You could also perform the VLOOKUPs in Excel or a database, then sync the results back to Smartsheet.

    We'd be happy to help you with this. Let me know if you need more help!

    Best,

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • What is a sheet rows limitation? 20k?

  • Hi @Petr Boublík

    Yes, Smartsheet has a limitation of 20,000 rows per sheet.

    Let me know if you have any other questions!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting

  • Thank you for your answer. I solved it by reducing lines in my report. But I ran into another problem. I need to copy cell content to another cell (in different column) with workflow. Or I need to fill in formula to cell with workflow at least. Unfortunatelly nothing works. I solved one problem and another problem occured with Smartsheet. I don't understand, why smartsheet is so limited. It can be very powerfull tool. Honestly, I'm trying to find better solution with Power Apps now.

  • @Petr Boublík

    Smartsheet has some limitations here. Unfortunately, it doesn’t allow workflows to copy cell content or insert formulas directly. You might try Data Shuttle or Cell Linking for basic data transfers, but if you need more control, Power Apps or Power Automate could be a better fit for advanced workflows.
    Happy to chat more if you’d like help exploring options!

    CHRISTIAN FINKE

    cfinke@digitalradius.com

    Schedule a Meeting