Assistance Needed with Cross-Sheet Formula Set Up

jaleeman

I’m trying to use a cross-sheet formula to pull data from my Primary Sheet into my Secondary Sheet in Smartsheet. My goal is to display the "Idea #" and "School Name" from the Primary Sheet in my Secondary Sheet for every row where the "Yes/No" column in the Primary Sheet has a value of "Yes."

Primary Sheet Details:

Column 1: Idea # (Contains unique identifiers for each idea)

Column 2: Yes/No (Contains "Yes" or "No")

Column 3: School Name (Contains the name of the school associated with each idea)

Secondary Sheet Details:

Column 1: Idea # (Where I want to display the "Idea #" from the Primary Sheet if the corresponding "Yes/No" value is "Yes")

Column 2: School Name (Where I want to display the "School Name" from the Primary Sheet if the corresponding "Yes/No" value is "Yes")

Formula I Tried:

=IF(YesNoReference@row = "Yes", IdeaNumberReference@row, "")

Note: I’m using a similar formula for the "School Name" column.

Additional Information: The Primary Sheet contains hundreds of rows, many of which have "Yes" in the "Yes/No" column. I want the Secondary Sheet to dynamically pull the "Idea #" and "School Name" into a row for each corresponding row in the Primary Sheet where the "Yes/No" value is "Yes."

Thank you for your assistance!

Yvonne Smythe

Hi @jaleeman - I’m not sure what you’re using the second sheet for but wanted to see if this would work for you instead:

1. Create a report based off primary sheet.
2. Pull in the columns you want.
3. Then filter Y/N column for Y

The report will automatically capture any row that flips to 'Y' off the primary sheet updates.

KPH

Hi

You will need to use cross sheet references to tell the formula on the secondary sheet to look at the Primary Sheet. You will also need something to tell the formula which row to return. Is your intention to have a row for each row in the primary sheet, or just to have rows for those where column 2 is yes?

INDEX and COLLECT functions are probably the ones that you will need.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/collect

For example,

In Column 1 You will index the Column 1 in the primary sheet, collecting rows where Column 2 is yes.

In Column 2 You will index the Column 3 in the primary sheet, collecting rows where Column 2 is yes.

You will need another thing in each Collect to identify the correct row to return each time. This could be the row number - so in row 1 on the secondary sheet, you return the details from row 1 on the primary sheet.

jaleeman

Thank you both so much for helping me think through this! Here's what I'm trying to achieve:

I am working with two sheets, one that is feed information by a form.

• Intake Backlog Sheet: This sheet collects information based on user submissions from an Intake Form. It acts as the central repository for all gathered data. The columns are numerous because there is a lot of logic built into the form. If an answer to a key question is "No" a certain set of questions populate. If the answer is "yes" an entirely different set of questions populate.

• Secondary Sheet (Immediate PL Need Intake): This is the sheet I am trying to build. I would like it to automatically pull select column information from the Intake Backlog Sheet for each row where the answer to "Immediate PL Need" is "Yes". Having this in sheet form (not a report or dynamic view) will allow me to create automated email notifications, allow stakeholders to engage with information in a row (editing columns to change stage, assign project manager, etc.), use the comments feature to communicate about projects directly in the sheet, link key project documents to the row, etc.

• Note: I need this transfer of information to happen automatically through a set-up formula in the Secondary Sheet. I don't want the information populating in the columns for a row to be dependent on the entry of an identification number for the row (like the Idea #).

• I've added a picture that mocks-up what I'm trying to achieve. I've had some suggestions of formulas I've tried, but none of them have been successful yet. I'm on the hunt for someone who can help me with the correct writing of one formula for a column. Then, I would be able to recreate it for the remaining columns. For example, can show me how to right a formula for the "Idea #" column in my Secondary sheet that would (in conversation language) say "For any row in my Intake Backlog Sheet that has a value of "Yes" in the "Immediate PL Need" column, place the value found in the "Idea #" column of that row in this cell."
  

jaleeman

Update - I may have fumbled may way into a formula that seems to be working, but It only returns back one tow's cell value from the main sheet (Intake Backlog Sheet). I can't seem to figure out how to write it so that all rows populate in the secondary sheet with the Idea # of rows in the primary (Intake Backlog Sheet) where the value in column "Immediate PL Need" is "Yes".

The cell formula's look like this:

=INDEX(COLLECT({Idea}, {Yes/No}, "Yes"), 1)

=INDEX(COLLECT({PLStatus}, {Yes/No}, "Yes"), 1)

Yvonne Smythe

Hi @jaleeman - I gave your requirements some thought yesterday and the needed functionality can be accomplished with an automated workflow that copies or moves desired columns/rows that meet the automated condition into a new document.