Best Of

Hi @Jpress,

That’s right, you can’t use absolute references such as $1 in column formulas. You could drag-fill the WBS target value down to all existing rows and then autofill will automatically populate new rows. 

However, my suggestion would be to create a sheet summary field and have users enter the WBS Target Value there, then reference that field in the formulas.

Your formula would then be:

• =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS([WBS Target]#, @cell))), Helper@row), "")

You’ll then be able to delete the WBS Target Value column from the sheet, since we have now moved the value to the sheet summary field. Your sheet should then look something like this:

Does that work for you?

Georgie

Hey @Jpress,

We can do that by adding in the CONTAINS function to the formulas in the Year 1 Distinct to Year 4 Distinct columns. However, we’ll need one more helper column to convert the WBS value to text, as CONTAINS does not work with numbers, only text strings (I used Paul’s comment here to help with this one - thanks @Paul Newcome!

So, next steps are as follows:

1. Create a helper column named “WBS - Text”.
2. In the "WBS - Text" column, use the formula =WBS@row + “” and convert to column formula.
3. Edit the existing formulas in the Year 1 Distinct to Year 4 Distinct columns so that they follow this base formula (I’ve made the section that’s changed bold so you can copy and paste this into the relevant spot in all 4 formulas):
   1. =IFERROR(INDEX(DISTINCT(COLLECT([Year 1]:[Year 1], [Year 1]:[Year 1], @cell <> "", [WBS - Text]:[WBS - Text], CONTAINS("123", @cell))), Helper@row), "")
4. Hide the “WBS - Text” column and re-hide the Year 1 Distinct to Year 4 Distinct columns.

That should do it - let me know!

Georgie

@Leibel S - THANK YOU SO MUCH - This works beautifully :)

@Cynde Ashcraft

See below, i added in the beginning an IF statement to check for a date, then i also changed your formula to make it a bit smaller by adding the year at the end only one time…

=IF(ISDATE([CLEAR Approval Date]@row), IF(MONTH([CLEAR Approval Date]@row) = 1, "Jan-", IF(MONTH([CLEAR Approval Date]@row) = 2, "Feb-", IF(MONTH([CLEAR Approval Date]@row) = 3, "Mar-", IF(MONTH([CLEAR Approval Date]@row) = 4, "Apr-", IF(MONTH([CLEAR Approval Date]@row) = 5, "May-", IF(MONTH([CLEAR Approval Date]@row) = 6, "Jun-", IF(MONTH([CLEAR Approval Date]@row) = 7, "Jul-", IF(MONTH([CLEAR Approval Date]@row) = 8, "Aug-", IF(MONTH([CLEAR Approval Date]@row) = 9, "Sep-", IF(MONTH([CLEAR Approval Date]@row) = 10, "Oct-", IF(MONTH([CLEAR Approval Date]@row) = 11, "Nov-", IF(MONTH([CLEAR Approval Date]@row) = 12, "Dec-", "")))))))))))) + RIGHT(YEAR([CLEAR Approval Date]@row), 2), "")

Try this:

=COUNTIFS({Response}, OR(@cell = "yes", @cell = "n/a"), {Assigned To}, @cell = "John Doe")

@Jason Tarpinian & @Darren Mullen

You all rock!!!! This community is the best part of Smartsheet! Thank you!!

Hi @Amber N,

If I am understanding this correctly something like this should work:
=IF([Was PCT Hospitalized within 14 days of COC?]@row = "Yes", [Date COC was Finalized]@row - [Date D/C from Hospital]@row, [Date COC was Finalized]@row - [Date COC Determined]@row)

This statement will check if that PCT Hospitalized within 14 days of COC is Yes, and if it is it will go off of Date discharged from hospital. Otherwise, if it is not Yes it will default to the COC Finalized - COC Determined.

Let me know if you run into any issues with this!

@Jpress use SumIFs to sum the Inv. Amt Y1 based on Inv. Year 1 and another SumIFs to sum Inv. Amt Y2 based on Inv. Year 2 and add the two sumifs togther like this:

SUMIFS(Inv Y1 data) + SUMIFS(Inv Y2 Data)

@Toufong Vang

Hi! I am also trying to utilize this as a work around for my sheet. I am struggling with the row being returned at the bottom of my sheet rather than it's original location. This throws off a lot of other formulas and make the sheet essentially useless. is there a way to return the cell back to it's original location?

For instance, if this was row 555 out of 895. When I test run this the row is sent to the other sheet and then returns back to my main sheet as row 895 not row 555 where it is needed.

@Nuno Santos

I just made a third sheet. A second Record Dates sheet and changed the formulas to refer to and run the operations for in progress on one sheet and complete on the other.