Cross-sheet formula trouble

BDK · 07/24/23

I am trying to create a cross-sheet formula. I want the formula in sheet 1 to check a row on sheet 2 for a boolean value (flag). If a cell in the row on sheet 2 contains a flag, I want it to return a value from another row on sheet 2. I have been writing what I think the formula should be to no avail.

=IF(ISBOOLEAN({Sheet 2 - Range 3}), {Sheet 2 - Range 2}, "")

Result I get is #INCORRECT ARGUMENT SET

I want the flagging of the cell in sheet 2 to be what triggers sending the content of the other cell to sheet 1. If there is no flag, then the other cell is blank and should be ignored....

Thanks in advance for any assistance!

Paul Newcome · 07/26/23

This is going to be a bit on the tricky side. The formula we would typically use to pull the list in from the source sheet would have the rows in the target sheet changing data as the source sheet is adjusted (think of it as row 1, 2, 3, 4, etc.). The challenge in your case is that you want to also add manual data in the target sheet for each of these rows. The problem with that is the manual data will not shift when the linked data does.

What you will need to do is use a Copy Row automation instead of formulas to push the data from the source to the target (unneeded column can be hidden on the target sheet).

In order to keep this sheet clean, you may want to use a sort of "archiving" type of move row solution to get rid of rows you no longer need.

Andrée Starå · 07/25/23

Hi @BDK

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

BDK · 07/25/23

Sheet 2, which is a meeting agenda where the information will be entered. Follow up actions, etc.

Sheet 2 contains a lot of other information, like agenda topics. Not all of them will have follow up actions. I want sheet 1 to include only the actions, who is responsible, due date, priority, etc. I also need the ability to add and store information in the sheet, which is why it will be a sheet and not a report... Anyhow, what I want is for the Follow up flag, or presence of an action in sheet 2 to populate sheet 1. I can then use INDEX to bring in information from the other columns.

Sheet 1 below.

Paul Newcome · 07/25/23

What is the possibility that the source sheet is going to be sorted or have rows removed or added at the top or in the middle of the sheet?

BDK · 07/26/23

https://community.smartsheet.com/discussion/comment/387005#Comment_387005

Sorting, probably not. Highly possible that rows will be added in the middle of the sheet on a regular basis. In the short term, I may add some columns as I develop the tool, but will not be a regular thing.

Paul Newcome · 07/26/23

This is going to be a bit on the tricky side. The formula we would typically use to pull the list in from the source sheet would have the rows in the target sheet changing data as the source sheet is adjusted (think of it as row 1, 2, 3, 4, etc.). The challenge in your case is that you want to also add manual data in the target sheet for each of these rows. The problem with that is the manual data will not shift when the linked data does.

What you will need to do is use a Copy Row automation instead of formulas to push the data from the source to the target (unneeded column can be hidden on the target sheet).

In order to keep this sheet clean, you may want to use a sort of "archiving" type of move row solution to get rid of rows you no longer need.

BDK · 07/26/23

Thank you for hour help, @Paul Newcome. It's clear what you are saying and I see this is the challenge I will run into. A workflow was the answer. When I make a change to one column, the workflow copies (not move) the entire row to my destination sheet. Then I hid the columns I don't want to see.

Paul Newcome · 07/26/23

Happy to help. 👍️

Cross-sheet formula trouble

Best Answer

Answers

Help Article Resources

Categories