Rows Cannot be Copied to Destination Sheet, Column Limit

I have successfully been able to use an automation in Smartsheet to copy a row from one sheet to another. While I have made some edits to both sheets, I am not getting the message "Rows cannot be copied to destination sheet because it has reached its column limit."

Clearly, the destination sheet not only has its columns but it has all the columns that have been copied from the source sheet previously when the automation ran earlier this week.

The source sheet is used to collect form submissions for new project proposals. When a supplier submits the form, we want to keep a comprehensive list of suppliers (their contact information and Form W-9 status) in a separate sheet. Over time, a supplier may submit forms for multiple projects, so we need the destination sheet to have one row for each supplier. The source sheet checks the status of the supplier's information with each new form submission. (Technically, only 8 to 10 columns of information are used from the source sheet in the destination sheet, but the Copy Row copies the entire row, so my destination sheet ends up having 200+ extra columns that are hidden.)

So, is the issue with the number of columns in the source sheet? Or in the destination sheet?

I have 270 columns (after exporting the sheet to Excel to easily determine number of columns) in the source sheet (and it has only 5 rows right now), and now that I've already done a row copy earlier this week, my destination column has 295 columns (270 + its own 25 columns), and it has 167 rows.

If 500,000 cells is the limit (i saw in another post), I should be able to copy well over 1,500 rows. So, why I am getting this error?

I concerned about this because I've built the entire workflow around being able to copy rows from the one sheet to another, and if I've already reached a limit and haven't even officially rolled out this new workflow, it's not going to be a feasible solution.

Find more posts tagged with

Accepted answers

Paul Newcome

The trigger is actually when a date is reached. Where it says "Run Once", open that dropdown and select "Custom".

All comments

Paul Newcome

That error message leads me to believe that it is an issue with the destination sheet, but you shouldn't be at the column limit. The column limit should be 400.

Unless there is some limit on the copy row automation itself, but I've not heard of that one. I know sometimes the scaling side of things doesn't always happen all at once. I can't remember all of the details, but I do remember seeing somewhere that certain features only worked with the Grid view limitations even though Table view supported a lot more rows / cells.

If you only need a few columns on this second sheet, you can set up a DataMesh or Data Shuttle.

The API or Bridge can also be leveraged to copy partial rows.

In more restrictive cases, you can use a "middle" type sheet that uses formulas with cross sheet references to only bring over those select few columns and then set up your copy row automation from there if you are wanting the data to remain static.

aschneiderheinze1025

If I used a "staging" sheet (a different destination in which the entire the row is copied from the source sheet) and then use cell reference formulas for just the few columns I need in my original destination sheet, how would the supplier's name get on the original destination sheet in the first place.

CURRENT

Sheet 1: Collects form submissions (a supplier can submit multiple forms, but checkbox is checked if that supplier's name/email does not appear in Sheet 2 (Supplier Directory), which is used as trigger to copy row

Sheet 2: Supplier Directory, comprehensive list of all suppliers, either added via a form submission (for supplier who hasn't submitted a project proposal yet to appear be in Sheet 1) or via a copy row automation from Sheet 1 when the supplier (name/email) is not already in Sheet 2.

PROBLEM: Getting the "Rows cannot be copied to destination sheet because it has reached its column limit." error.

REVISED

Sheet 1: Copy row automation copies to Sheet 3 (Supplier Directory (Staging)) when the unique checkbox is checked.

Sheet 2: Uses cell references in the 8 to 10 columns needed for suppliers that have been added to this sheet because they are (or were at one point) unique in Sheet 1.

Sheet 3: Includes rows copied from Sheet 1, when unique checkbox was checked in Sheet 1 to trigger the copy row automation

I like this approach cause it keeps Sheet 2 from having a bunch of hidden columns, especially if columns are added to Sheet 1 and are not automatically hidden in Sheet 2 when copied.

QUESTION: How does the supplier added to Sheet 1 that is unique get added to Sheet 2 in the first place, in order for the cell references from Sheet 2 to Sheet 3 to work?

I wish there was a way just to copy one column. I have a "key" column that if only that could be copied, then I would be able to use cell references for the other columns I need in the sheet.

I could actually use this same approach to copy the key to a payment sheet, which I bet will cause a similar copy row error since that payment sheet already has 350 columns, and adding the 200+ columns from the source will make it huge.

Paul Newcome

You would "pre-fill" a bunch of rows on the middle sheet. The first column (I usually call it "Number") starts with 1 on row 1 and goes on down the line until you have as many as you think you will need plus a bit of a buffer. There are additional solutions to add more rows or alert you if you start running close to the end of your pre-populated rows.

so on and so forth.

Then you would use an INDEX/COLLECT or something of the sort to pull values from the key column based on whether or not the supplier in Sheet 1 is unique. I believe you said you already have a checkbox for this, so it would look somethign like this:

=IFERROR(INDEX(COLLECT({Sheet 1 Key}, {Sheet 1 Unique}, @cell = true), Number@row), "")

Now that you have your list of unique keys, you can index/match on them to get the other columns on over.

aschneiderheinze1025

Thanks for your time to help me think through this, but I am not sure I understand how this gets a unique supplier from Sheet 1 on to Sheet 2, so that I can use cell reference formulas in Sheet 2 to access information copied from Sheet 1 in Sheet 3.

Sheet 1: Project proposal form submissions (a supplier can submit multiple project proposals)

Sheet 2: Supplier Directory

Sheet 3: The "middle" page you are referring to (preventing the entire row from Sheet 1 being copied to Sheet 2).

I am not sure what you mean by pre-filled rows.

If a supplier (say, "John Doe") submits a form and is added to Sheet 1, there are other columns in Sheet 1 that the program manager uses to "manage" the project proposal. The Sheet has over 250 columns (aside from maybe the 12 to 15 from the form itself.) One of the columns is a checkbox that checks to see if John Doe (with his email) appear in Sheet 2. if not, the box is checked.

When the box is checked, currently, the row is copied to Sheet 2. All Sheet 2 needs to do is keep track of suppliers, their contact information, and the status of their Form W-9. We only need the supplier to be listed in Sheet 2 once, which is why I use the unique checkbox in Sheet 1 to prevent copying the row if the supplier already exists in Sheet 2.

Initially, I understood that you suggested instead copying the row to Stage 3. this would be great to avoid having all those extra unnecessary columns in Sheet 2 (which are hidden, but if the program manager adds any columns in Sheet 1, they are copied and won't be automatically hidden).

Then, i could use cell reference formulas in Sheet 2 to pull the now static information in Sheet 3. The "match" would be the name and email of the supplier listed in Sheet 2.

However, I don't understand how the supplier gets to Sheet 2 in the first place, if the row was copied to Sheet 3. Sheet 3 will be all unique suppliers (cause the automation in Sheet 1 controlled that), but I need the name and email of the supplier in Sheet 2 initially before any index-match to Sheet 3 would work, right? How can the name/email get on Sheet 2 if the row was copied to Sheet 3?

Paul Newcome

We don't need three sheets.

Sheet 1 is the form intake. Sheet 2 is the Suppliers list.

We have two options for populating the Suppliers List, but which route is going to depend on your needs.

Do your users fill in the email and phone on every row in the intake sheet even for the same suppliers, or are you wanting to have a list of suppliers where you can enter their email and phone the one time and have sheet 1 do a lookup to pull it over after a form has been submitted?

Forgetting number of sheets, number of columns, rows, etc…. What is the overall goal of this?

To get a unique list of suppliers and their info from the intake sheet

To get a unique list of suppliers where you manually enter the info once per supplier that then gets pulled back into the intake sheet via lookup?

aschneiderheinze1025

Each time a supplier submits a project proposal, the form asks them for their name (parsed by last, first, middle) and email address. So, those could appear across multiple rows in Sheet 1.

Sheet 2 should have one row for each supplier. Ideally, the name/email that is "copied" from Sheet 1 to Sheet 2 would match the name/email for that supplier in every project proposal they submitted. If not, we would figure that out while managing the Supplier Directory.

The ultimate goal is (1) to have a sheet to manage project proposals, this is sheet 1 and (2) have a comprehensive list of suppliers, some of which were added because they had submitted project proposals and some of which were added via a form in Sheet 2 because they do other work in our department that isn't tied to a project proposal; in both cases, the supplier is paid via a W-9 form, and the Supplier Directory helps to document/track this form.

As suppliers submit project proposals, I want to be able to automatically reference Sheet 2 to indicate if (1) they are new and need to be added to Sheet 2 or (2) they have already submitted a Form W-9 and that Form W-9 is valid.

As I mentioned, some suppliers are added to Sheet 2 via a form in Sheet 2 (a New Supplier Onboarding Form) and others are added automatically because they submitted a project proposal in Sheet 1 (via that form New Project Proposal Form).

The finance team manages Sheet 2. Program Managers manager Sheet 1.

While I thought copying a row from Sheet 1 to Sheet 2 would get those new suppliers (and their contact information (around 8 to 10 columns) into Sheet 2 automatically (if they are submitting a project proposal for the first time. The information can be static in sheet 2 (that is not a cell reference). Contact information for the supplier would be managed by the finance team in Sheet 2.

I employed a similar methodology for another sheet. Those project proposals are also copied to a "build" project management sheet —- but really the "key" column (a unique project proposal #) is copied to that sheet, and the columns have cell reference formulas so the information in that sheet stays current with changes that are made in Sheet 1. This sheet is used by another team.

So, that's where it gets tricky —- it can be static in Sheet 2 but dynamic in this other sheet. And its only a few number of the columns in Sheet 1 that I need, not all 200+ columns, but as I understand a Copy Row automation copies an entire row.

Paul Newcome

Ok. So we are going to want to insert a text/number column in Sheet 2 called "Number". Prep-populate this with the numbers 1 through however many you think you will need. If you are anticipating no more than 50 suppliers, maybe go to 100 just to be safe.

In the next column over will be the suppliers.

=IFERROR(INDEX(DISTINCT(COLLECT({Sheet 1 Suppliers}, {Sheet 1 Suppliers}, @cell <> "")), Number@row), "")

The next column over would be the email address.

=IFERROR(INDEX({Sheet 1 Emails}, MATCH(Supplier@row, {Sheet 1 Suppliers}, 0)), "")

Next column over will be a flag type column.

=IF(COUNTIFS({Sheet 3 Suppliers}, @cell = Supplier@row) = 0, 1)

Set up a copy row automation on this Sheet 2. It will have to be time based, but it will have a condition of this flag being flagged. Copy to Sheet 3. Sheet 3 would also be populated via the "New Supplier" form.

Of course any other fields that need to be pushed from Sheet 1 into the directory would also be brought over to Sheet 2 via INDEX/MATCH the same way the emails are.

aschneiderheinze1025

This is an interesting idea!

So, just to say in my own words what you are suggesting.

Sheet 1: Current sheet collecting form submissions from supplies with project proposals.

Sheet 2: A "supplier" staging sheet that lists unique suppliers in Sheet 1, and includes other relevant columns needed for the Supplier Directory that are populated via cell references to Sheet1. Includes a column that "flags" the row if the supplier is currently not in Sheet 3. Includes automation that copies the row (now 8 to 10 columns versus 200+ columns) to Sheet 3.

Sheet 3: Supplier Directory that accepts for submissions for new suppliers (as it currently does) and rows copied from Sheet 2 that are not currently in Sheet 3. The Finance team can manage the information in this sheet because every column is editable (even those from Sheet 2 because only the values are copied, not the cell reference formulas.

Does DISTINCT require the [Number] column to already be there? And these have to be manually added (50, 100, or whatever), meaning someone needs to know at some point to add more rows if over time the number of distinct suppliers submitting project proposals to Sheet 1 may exceed the number of rows manually added. Or, could DISTINCT exclude those in Sheet 1 with a checkbox that is checked ONLY when it does not appear in Sheet 3? This means the DISTINCT list in Sheet 2 would not grow per se because the number of unique AND not in Sheet 3 will stay minimal?

aschneiderheinze1025

Instead of finding distinct Supplier names, it could find distinct Supplier Keys, and in Sheet 1, the Supplier Key could only be there if the box is checked (its already in Sheet 3). Then the other columns in Sheet 2 pull the Supplier Name, etc. and all are copied to Sheet 3.

aschneiderheinze1025

@Paul Newcome This solution is working perfectly. I noticed the automation when a row is flagged in the Helper/Staging sheet doesn't run. You said: "It will have to be time based". Why would it need to be time based? You can't use when row is changed/added, and that column is "flagged" or even when modified date changes to any value and that column is checked?

When I set up the automation, I get an error saying I can't use "hourly":

I'll just mention that in this sheet, I have a column that has

Number
the DISTINCT formula
the flagged column (used in the automation)
a cell reference formula to return a value from the other sheet (needed to be copied with row)

I think I read that an hourly/daily/weekly trigger can't be used when its based on a column with a formula, which in this case it is. I tried use the Modified Date system column, and I get the same "Workflow timing is unavailable" message. No one should have to access this helper/staging sheet, so every column needs to be a system column or a column with a formula.

Paul Newcome

It has to be a time based trigger (not column based) because the formula has a cross sheet reference in it. You will have to remove the flagging trigger and use it as a condition instead.

aschneiderheinze1025

I assume I should use "When rows are added or changed"… What do I put for "When" for when something changes? I tried leaving this blank and put hourly for run workflow, and I get that message still.

Paul Newcome

The trigger is actually when a date is reached. Where it says "Run Once", open that dropdown and select "Custom".

MichelleWatkins_GPP

@aschneiderheinze1025 - I know you probably desire to stay within the Smartsheet core app, but I would definitely recommend tapping into either Smartsheet's premium app, DataMesh, although it can get expensive or tapping into Smarter Business Processes (SBP) more cost effective apps such as their Smarter Row Copy.
Here's the link to SBP's app page: https://www.smarterbusinessprocesses.com/smarter-apps.

Summary of SBP's Smarter Row Copy:
Copy rows between sheets with just the columns you need
No more copying entire rows
Include row attachments
Maintain data synchronisation between copied rows
Define your own trigger for the process

» We recommend SBP apps to many of our clients, especially Smarter Dropdowns and Smarter Backup apps.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.