Best Of

Hi Dayle

Is this what you are looking to do?

If so, the Formula in the col Parent Info would be:

=PARENT([Info from Parent]@row)

I hope that helps. If it's not what you are looking for, please let me know.

When rows move into my sheet, I use them as parent rows for my projects. I want to have a column formula which automatically pulls in data from the parent row to its child rows. I've tried putting =PARENT() into a child row but it makes all cells in that column empty when I convert it to a Column Formula.

Any assistance would be hugely appreciated - I'm new to Smartsheet forumlas, I've been using Automation as a workaround but this has limitations.

You would first need to convert the bars into numbers. The easiest way to do this would be to add a series of COUNTIFS together.

=COUNTIFS([Helper 1]@row:[Helper 2]@row, @cell = "Quarter") + (COUNTIFS([Helper 1]@row:[Helper 2]@row, @cell = "Half") * 2) + ......................................

Then you would take that and divide it by the number of cells that aren't blank.

=(COUNTIFS(.....) + (COUNTIFS(.....) * 2) + .....) / COUNTIFS([Helper 1]@row:[Helper 2]@row, @cell <> "")

I would suggest leaving this in its own column because from here we need to use a nested IF. Dropping this into a nested IF will provide for a single column formula, but it will be rather large and unwieldy. Putting the next piece in its own column will make things much easier to manage and troubleshoot.

=IF(Formula@row>= 4, "Full", IF(Formula@row>= 3, "Three Quarter", IF(..............

Hi @twallen,

Just add a

HAS() to your formula and you will be all set - this is needed for the multi-select field.

=SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"))

Later, to add the year part, use this.

=SUMIFS([Total CE Units]:[Total CE Units], Employee:Employee, "MB", [Applicable Categories]:[Applicable Categories], HAS(@cell, "HSW"), [Date Earned]:[Date Earned], @cell <> "", [Date Earned]:[Date Earned], YEAR(@twallen

Hope this helps,

Dave

Hi @Esquared213

Lets try two different formulas to determine where it's having trouble:

=SUM([Annual Revenue]:[Annual Revenue])

and

=COUNTIFS([Fiscal Year]:[Fiscal Year], 2023)

These should both return a number. If one of them is giving you an error or incorrect result, this will help us narrow down where it's getting stuck!

Is there a reason you are doing it this way instead of turning on the dependencies within the sheet?

@Siobhan16

Great! I also use IFERROR when indexing reason is the #NO MATCH is set by SS and we do not know what value they are using to set it. So when you have #NO MATCH it also prevents you from using conditional formatting. We use a lot of indexing to update over 700 Pharmacy Sites in tons of sheets and when a site closes it is removed from our Reference API so it cannot match the site # once it is removed I get the #NO MATCH so I set the " " or "Site Closed" or "VACANT" as the value if False.