List one sheet to another when column contains text

Bruce Johnson

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

Paul Newcome

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))

Bruce Johnson

Works like a charm thanks Paul. Appreciate the great advice.

Paul Newcome

Are you able to provide screenshots with mock data in them?

Bruce Johnson

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

S4_RTM_All_Combined.xlsx

Paul Newcome

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.

Bruce Johnson

Sheet 1 Source sheet

sheet 2

thanks Paul

Paul Newcome

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))

Bruce Johnson

Works like a charm thanks Paul. Appreciate the great advice.

Paul Newcome

Happy to help. 👍️