Formula Help!?!

brianw

Hi all

I am trying to write a simple formula to pull data from different sheets. Essentially if sheet 1 is answered yes, then I want to take data from sheet 2 and place it in sheet 3.

Here is the formula I am currently trying but keep #invalidoperation.

=IF({Copy of Appendix B Approval Range 1} = "Yes", "{Appendix B - Dakota Action Items Range 1}", "")

Where Copy of Appendix B Approval Range 1 is a column and Dakota Action Items Range 1 is a cell.

I can get it to work as is if I change Copy of Appendix B Approval Range 1 to a cell but that wont work when rows are added to the sheet.

Any help is appreciated!

Genevieve P.

Hi @brianw

Does it matter where the "Yes" is placed in your first sheet? Are you looking in the entire column for if there is at least one "Yes"? If so, I would write a COUNTIF statement first:

=IF(COUNTIF({Copy of Appendix B Approval Range 1}, "Yes") >=1

This says, if there is a COUNT of at least 1 yes in the column in this other sheet, then....

=IF(COUNTIF({Copy of Appendix B Approval Range 1}, "Yes") >=1, {Appendix B - Dakota Action Items Range 1}, "")

IF the COUNTIF statement is true, it will pull back your one cell. If there are no cells with "yes" then it will be blank.

If I've misunderstood what you're looking to do, and you're actually trying to match up multiple rows that could have a "Yes" across sheets, then it sounds like instead of a COUNTIF you'll want something like an INDEX(MATCH which will return a value across sheets when it finds a match in your unique identifiers. Here's another Community Post that goes in to more detail.

If neither of this helped, please post screen captures but block out sensitive data and I'd be happy to take a look.

Cheers,

Genevieve

brianw

Thanks Genevieve! Here is a more detailed explanation of what I am trying to accomplish, which is hopefully better.

I am pulling data over to Smartsheet from MS Forms using Power Automate. I like the approval workflow in Smartsheet much better than Forms.

However, by using MS Forms as my initial data collection I am able to have it insert rows based on user responses. For example, if the user says "Yes" there are additional installation costs outside of this request Power Automate inserts a row on an "Action Log" within Smartsheet that has predetermined text "Review additional costs with user" so I can track any potential gaps during installation.

The issue I am running into is if the initial submission needs to be updated for any reason (i.e. user input error) I cant get Power Automate to generate the action item because its only completed upon the MS Form submission.

My thought was I could use the update form automation workflow to review and approve the submission in Smartsheet and then possibly use a formula to look at each entry (row on Smartsheet) and depending on the answer (Yes or No) of each column, I could pull a predetermined text from a corresponding helper Smartsheet and put it in the "Action Log"

I am very close to this being fully functional, but have to get over this hurdle on updating responses.

Edit: I should add I am also not very tech savvy when it comes to programming or anything. I have only really ever worked in Smartsheet.

Genevieve P.

Hi @brianw

Thank you for explaining your process further!

Instead of using a formula to pull into your Action Log, what about using a Report to filter the rows based on "yes" and surface specific columns?

You can still have the update request run from your initial Intake Sheet, and you can add the cross-sheet formula into this initial sheet as well.

Can I ask more about this text? Will it be the same thing every time? If so, we could either use a formula to input it directly without needing your helper sheet, or a Change Cell Workflow to auto-add it in.

brianw

I did not think about a report function that may work! I will play around with that!

The text will be the same each time the question is answered yes. I should also tell you I have ~50 questions that each will have a standard predetermined text output if applicable. For example, every submission that the user says "Yes" there are additional installation costs outside of this request will get the same predetermined text "Review additional costs with user".

I really appreciate you taking the time to help with this! I have been pulling my hair out trying to figure something out.

Genevieve P.

Hi @brianw

I would still suggest that building out that text in a direct formula will be easier for the sheet to process... even if it's through 50 cells! This is because it won't need to search another sheet for the answer.

Try something like this:

=IF([First Column]@row = "Yes", "Review additional costs with user")

And then again:

=IF([Second Column]@row = "Yes", "Review additional costs with user")

=IF([Third Column]@row = "Yes", "Review additional costs with user")

brianw

I tried =IF({Appendix B Initial Approval Flow Range 1}@row, = "Yes", "Review Additional Costs with User") where {Appendix B Initial Approval Flow Range 1} I clicked the column header. It gave me an #UNPARSEABLE error.

Do you mean to add the action item description to my intake sheet?

Genevieve P.

Hi @brianw

In this instance you would only need 1 sheet, not 3. The cell you're referencing to check for "yes" would be the one in the same sheet, either to the left or right! Then your Report would be the "second sheet" and the formula would replace needing a third sheet. Does that make sense?

brianw

Ahhh yes sorry I have been struggling for awhile so I am overthinking it.

If I want to be able to assign people and due dates to the actions with automatic notifications could I still pull them to a different sheet just for a cleaner view? I dont believe I can automate workflows on a report.

Genevieve P.

You're correct, you can't build automations on a Report. However you can still build the automation on the same source, underlying sheet. Then in the Automation you could add a link to the Report in the Message so when they navigate into Smartsheet you could direct them to the "cleaner view".

brianw

Thanks Genevieve you have been a great help and got me further than I have been for sure!

Do you know if there is a limitation to the number of columns I can have in a sheet? Do put everything in one sheet is going to be a lot of columns as the ~50 questions each will have an action, plus a responsible person, due date and a status.

That is why I was trying to move the action item to a different sheet for cleaner automation. I would like to have it email the person responsible once assigned to alert them of the assignment.

Edit: I also cant have duplicate columns showing due date and responsible person in one sheet to assign each action item too.

Genevieve P.

Ah, thank you for clarifying. It sounds like this process is quite complex if you have 50 separate items to assign to 50 different people, each with their own due dates and status, etc.

There is a limit of 400 columns in a sheet, and a cell limit of 500,000 (see: System requirements and guidelines for using Smartsheet)

I have to say that instead of inserting a new row into one sheet using a form, it sounds like you maybe need to create one sheet per submission, with each of your current columns actually being set as Rows Instead. This would give you 50 Rows of Data to track one project, then one column for all your "Yes" / "No" per-row, one Due Date column, one Status column, etc.

It does mean you'd have to create an entirely new sheet per-submission. You could have a template set up so it's easy to generate, with all of your automations and potential formulas pre-populated, but there would likely be a bit of manual work to bring through the information that came in via the form into your intake sheet.

If you want to create a second sheet to track all the other columns (Status, Date, for your 50 questions), then you would need to have a unique identifier in both sheets for the formula to reference and match rows... otherwise it won't know what row to pull data from. This would still give you a second sheet with many columns and it would require creating an individual cross-sheet reference per "Yes" that you're looking for. (Here's the article with cross-sheet limits).

An alternative could be to automatically Copy Rows from the source sheet to another, splitting up your original source data into say 5 sheets, each with 10 questions. You can have a Condition block set up so it only copies to the relevant sheet if one of those 10 columns says "yes". Then these 5 other sheets can hide the 40 columns from the original source that are not needed, and focus just on the 10 columns that are relevant to that sheet. Let me know if this makes sense!

brianw

It is definitely a pretty complex process flow I am trying to build, especially because I am not all that tech savvy. That does make sense and I am little hesitant trying to do the complete build-out with some of the limitations. I have also posted on the Power Automate Community to see if there is some work arounds available through that system as well.

I cant thank you enough for taking all of the time you have to help me out!