How to connect sheet to multiple datatables

Petr Boublík

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

ChristianFinke

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,

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.

Petr Boublík

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.

ChristianFinke

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,

Petr Boublík

What is a sheet rows limitation? 20k?

ChristianFinke

Hi @Petr Boublík

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

Let me know if you have any other questions!

Petr Boublík

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.

ChristianFinke

@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!

Lindsay Whitbread

Make.com is another option to do more complicated automatic cell copy type work in Smartsheet. We've got it working nicely in quite a lot of different use cases.