Sync columns across sheets

I created a workspace for requesting/approving/tracking projects. If a project request is declined I move it to another sheet.

When I made the new sheet I copied the columns so initially things were sync'd.
When a project moves to the declined sheet, any missing columns are added at the end (far right) so things stay somewhat sync'd.

I've run into a few inconveniences:

When a column formula is updated on one sheet the other sheet doesn't know to also update, so the same request can result in a different calculation depending on the sheet it's on.
As I add new columns I have to do it in both places. Or, if I let the columns auto-create when a project moves to the other sheet, the column order isn't maintained.

I tried looking if there was a 'Sync' feature for sheets I'm missing and all I could find was how to sync a cell (cell link). Is there anything I may be missing?

Thanks for any help!

Find more posts tagged with

Sheets

columns

Accepted answers

All comments

KPH

I assume you are just using the core product.

When you move a row, you take a static copy of that row and put it somewhere else. Any changes made to the original sheet will not be applied to the moved row as they are not linked in any way.

One alternative method would be to leave all the data in the original sheet. Use a filter to exclude all the declined projects from being visible. Then create another sheet and use a VLOOKUP or INDEX to display only rows that are declined. This would solve issue 1 but not issue 2 (and a VLOOKUP could make issue 2 worse). I can't think of a way to automatically add columns to a second sheet in the same order as they appear in the first.

PM_Reeves

Hi @electrichead ,

I use a system similar to what you describe.

I created my 'Project Creation - Awaiting Approval' sheet with all the required columns.

Then 'Save as New' and called this 'Project Creation - Approved/Declined'

I then built a form for the former, so that all the information gathered would go to a specific place in the target sheet.

I added the automation Move a row to another sheet when specified criteria are met for when my 2nd level approval was granted or declined.

I've just gone through the whole process a second time whilst trialling Dynamic View, so it can definitely work.

Hope that helps.

electrichead

Thanks @KPH,
Yes, I originally had everything remain on one sheet. But with the data I'm ingesting I was worried about hitting the cell limit too quickly. Besides the core product I have access to Data Mesh, Dynamic View, Data Shuttle, Pivot, and WorkApps if any of that makes a difference.

Also, Thanks @PM_Reeves,
What you described is basically what I did as well, and has gotten me to this point. I think you'll like the Dynamic View and it's outside my question but some things that tripped me up I'll mention to you: Don't forget to add what domains have access and Share a sheet level filter set for current user to help customize/limit the users view within the Dynamic View.

KPH

@electrichead I am no expert with the premium apps but I think you might be able to do something with Data Shuttle. You could try posting in that topic and see if anyone there has some tips. Good luck!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.