By formula you can do this, but you will need to add an additional Row ID column (1,2,3,4,etc.) and can then use:
=INDEX(COLLECT({Project ID}, {Dropdown}, "Automation"), [Row ID]@row)
Pull Data from Another Sheet based on Criteria
ahsan_mazhar101
✭✭✭
Hello,
Below is my master file of projects. I've filtered to show all the values based on "Automation" in the Drop Down column. Each project is designated with a project #.
I want this sheet to function as a way to automatically carry over all the project #s from the master file to my new file (see below) based on all the projects designated as "Automation" in the Drop Down column.
For context, I tried to use INDEX MATCH based on the matching for "Automation". However, it's only capable of pulling over the first value.
Is there any way for me to achieve this task without creating a workflow?
Best Answers
-
By formula you can do this, but you will need to add an additional Row ID column (1,2,3,4,etc.) and can then use:
=INDEX(COLLECT({Project ID}, {Dropdown}, "Automation"), [Row ID]@row)
-
This worked for me! Thank you so much.
Answers
-
Use Smartsheet's inbuilt Automation to help you here - have the rows moved to your 2nd sheet when they are created with a workflow like this:
Once you've run it once, you should then have the columns from the actual sheet in the new one (remove any surplus ones) and can then use INDEX MATCH on each column using the project number (if you've no duplicates) as a reference to keep the data updated.
Hope this helps, but if you've any problems/questions then post up what they are.
-
I don't have administrative permissions on the master sheet to develop a workflow. Would it be possible to create the automation through alternative means (e.x. formulas)?
-
By formula you can do this, but you will need to add an additional Row ID column (1,2,3,4,etc.) and can then use:
=INDEX(COLLECT({Project ID}, {Dropdown}, "Automation"), [Row ID]@row)
-
This worked for me! Thank you so much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …