List one sheet to another when column contains text
Hi Smartsheet Guru's
I have a sheet with many columns and formulas - which is increasing in size and slowing down. I would like to offboard some of the formulas to another sheet. Therefore, I would like to create a formula that looks in column Gap ID and if it has text (not empty) then the formula would copy the text in the workstream column for that row and display it in the secondary sheet.
So Sheet A
Column WS Column GAP_ID
1 STP Yes
2 STP
3 OTC Yes
4 OTC
5 RTR Yes
6 RTR
7 PTC Yes
8 PTC
Sheet B
Column WS
Need formula to display
1 STP
2
3 OTC
4
5 RTR
6
7 PTC
8
I just need the starter formula as I will use it to bring over other pieces of data in different columns and build separate sheet to do the KPI's so the main source won't have to process then as the team works on the source.
thanks Bruce
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Best Answers
-
Ok. You will need to add a column that replicates the row number and "pre-fill" the sheet so that you have as many rows numbered in sheet 2 as you do in sheet 1.
Then you would use a formula along the lines of...
=IF(INDEX({Sheet 1 Gap ID Column}, [Row Number]@row) <> "", INDEX({Sheet 1 Gap ID Column}, [Row Number]@row))
-
Works like a charm thanks Paul. Appreciate the great advice.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Answers
-
Are you able to provide screenshots with mock data in them?
-
Hi Paul
Attached the source sheet - what I am trying to get to is have another sheet that will look up the Gap ID column if there is a value (text - such as OTC-G-004) in that column then will place the row equivalent in WS in the secondary sheet. Thanks Paul
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
Can you provide screenshots of exactly what you are working with in Smartsheet with mock data to replicate the desired output? I am still not following what you are trying to accomplish exactly.
-
Sheet 1 Source sheet
sheet 2
thanks Paul
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
Ok. You will need to add a column that replicates the row number and "pre-fill" the sheet so that you have as many rows numbered in sheet 2 as you do in sheet 1.
Then you would use a formula along the lines of...
=IF(INDEX({Sheet 1 Gap ID Column}, [Row Number]@row) <> "", INDEX({Sheet 1 Gap ID Column}, [Row Number]@row))
-
Works like a charm thanks Paul. Appreciate the great advice.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!