Copy Rows with Smartsheet Index Match Formula
Vložit
- čas přidán 14. 02. 2023
- Use the Index/Match formula combination to pull data from one Smartsheet sheet to another automatically.
I’ve used this formula combination in almost every Smartsheet implementation I designed for clients. It updates in real time without having to worry with automations or advanced Smartsheet tools to kick in.
📰 Plan your Smartsheet implementation like a Consultant: WorkflowCreative.com
The only Smartsheet book I recommend: amzn.to/45fUu1A
DUDE!!! Spent 3 hrs last night using vlookups to sort, and one 7 min video will save me hours sorting export csvs.... You are an AMAZING Teacher and now I gotta see all of your other tips vids! One Million THANK YOUs Workflow Creative!
Ashley, you're welcome! So glad to hear that someone found some use out of these vids. haha! Made my day.
Thank you so much for this! I looked at so many videos trying to figure it out and yours helped it click for me! I was able to do exactly what I wanted. Subscribed! :)
Whoop! Great way to start the weekend. 😊Thanks for letting me know and I’m glad it helped! -Ryan
duuuude this is amazing. I was linking cells one-by-one across 50 sheets, what a great video
Whoop! Glad to hear it helped! - Ryan
I spent entirely too much time trying to figure this out on my own haha THANK YOU
I heard that! I do the same with new tools. Glad it helped though! - Ryan
I really appreciate this video! So well described! I've been struggling with this formula for months off and on trying to get this to work.
Oh wow! So glad it helped. I know the feeling of struggling, so I'm just trying to help pass along the shortcuts :) - Ryan
what an explanation thanks man
you bet! glad you enjoyed it! - Ryan
This was a very informative video. Question, would this be able to be applied to drop-down columns?
You can! But anytime you add a column formula to a drop-down column, you lose the drop-down feature. What’s your scenario? - Ryan
First thing..thanks alot for your videos..very much useful.... Need advise..example I have 10 sheets for each client and converted those into reports to get the information in one place. So I need to have that report data into one sheet so that I can build a dashboard. Will there be anyway to shift the data from reports to one sheet.
In core Smartsheet: Data flows from sheets to reports only; If you have the Smartsheet add-on called Data Mesh, you can use the report as the source, then send the data to your sheet. -Ryan
@@workflowcreative oh that's great!Do we have any of your demo videos which explain the process..that would be life saviour😍
@@ganeshap23 I don't yet. Sorry
Thanks a lot 👍 appreciated
I have a Summary raw Data report hence unable to use data mesh. I cannot use the Row data report because it doesn't pull summary data.
Hi, will this work for my use case which is , I need my data to move from one smartsheet to another smartsheet. But all the column names are different. What is the thing I need to do first?
Hi Shaukhi, If you have access to the premium app called Data Mesh, use that. If not...First you'll need a "key" between the two sheets. This could be a Property ID, an inventory number, a street address, etc. It needs to live on both sheets. Then, start building your index/match formulas like in the video for each of your columns on your second (Destination) sheet. You'll need to create a Cross Sheet Reference (right click, manage references) on your Destination sheet for each of your columns on your first (Source) sheet.
Great video! I was able to get this to work for my sheet, but I use this to show if someone completed something, which means sometimes there is no match to be found. Right now my cell will show as #nomatch if it can't find a matching email from my source sheet. How do I make it show just a blank cell instead?
Hi Ellyn, you can wrap your formula with IFERROR so =IFERROR( your entire formula goes here , "") Let me know if that's what you're looking for -Ryan
Can we establish a formula which replaces the last updated status with current status. Like it has to be dynamic, everytime I make changes in current status, the last updated status values get replaced.
Hi! What does your solution entail? 2 sheets? 1 column called “Status” on sheet1 and 1 column called “Status” on sheet2?
Ok then??
Then you would use the index match formula on sheet2 (like in the above video) with a “key” that connects the two sheets rows (a key is a column that holds unique values like SKU, product number, work order number, etc. ) That index match would update your status column in sheet2 based on the data in sheet1. Does that help?
Is there any way I can connect with you 1:1? @@workflowcreative
What’s the advantage to Vlookup?
With vlookup, your formula will break if a column is inserted in your range or someone moves your columns on your sheet. With index/match you specify both the lookup column and the results column. This way, they can move around the sheet as much as you need and the formula stays intact. -Ryan