Best Of

I figured out that the problem arose in the how I had my cells formatted that I was trying to subtract. The problem has been resolved. Thanks!

I would adjust the formatting in the column to remove decimal places. Then the formula result will automatically show the rounded value for you.

Hi @sleepysapling,

You can achieve this by combining the IF function with an INDEX(MATCH) formula. 

For INDEX(MATCH) formulas to work, you need to have a matching value that ​​must be unique across both sheets and stored in the same column type. That is, the value will be unique to one row in each sheet but match exactly between the two sheets, so that the MATCH function can locate the relevant row. 

It’s not clear from your screenshots if you already have this in your sheets, so if you don’t, you could use either an auto-number column or a column where you combine values from other columns in your sheet, eg a Row ID + Task Name (your formula for that column would be something like =[Row ID]@row + “-” + [Task Name]@row, but using the relevant column names from your sheet, and you can add more columns into the formula if desired). 

In my test sheets, I’ve used an auto-number column to create my matching values, and I’ve called this “Task ID” in the source sheet and “Task number” in the destination sheet.

I set my source sheet up with similar values to yours, as seen below:

Then, in the Project Sponsor Approval column in my destination sheet, I used the following formula:

• =IF(INDEX({Status}, MATCH([Task number]@row, {Task ID}, 0)) = "Complete", "Approved")

The elements in curly brackets {} are cross-sheet references. You can name these as desired, but to clarify, the references in the above formula are as follows:

• {Status} is the entire Status column in the source sheet 
• {Task ID} is the entire Task ID column in the source sheet

You can see the formula in the destination sheet here, showing that it’s also referencing the Task number cell in the same row as the value it needs to find a match for in the source sheet:

I then converted this to a column formula and confirmed that the approval column updates as desired - displaying “Approved” if the matching Task ID in the source sheet is set to “Complete”:

You can check out the following resources for more information on the functions within the formula and how to use different functions together, such as INDEX and MATCH:

• IF Function
• INDEX Function
• MATCH Function
• Formula combinations for cross sheet references

Hope that helps!

Georgie

Joseph & Gillian - Thank you for the responses. Unfortunately, we do not have DataMesh available so I will set up the automation to trigger on a time basis as Gillian suggested. Thank you again!

Thank you @Gillian C - thats a great idea on Option 1, i never thought of that! appreciate it!

Hi @KIMST

Great question! You're definitely on the right track for automating weekly and monthly reporting. Here's a way to do it using the Close Date column,

Step 1: Add helper checkbox columns

https://app.smartsheet.com/b/publish?EQBCT=596595fa4d484e62be27d195c0caa344

Add two new checkbox columns to your sheet:

• This Week
• This Month

Use these formulas:

This Week:

=IF(ISDATE([Close Date]@row), AND(WEEKNUMBER([Close Date]@row) = WEEKNUMBER(TODAY()), YEAR([Close Date]@row) = YEAR(TODAY())))

This Month:

=IF(ISDATE([Close Date]@row), AND(MONTH([Close Date]@row) = MONTH(TODAY()), YEAR([Close Date]@row) = YEAR(TODAY())))

These formulas check if a row has a valid Close Date and whether that date falls within the current week or month. They also handle year-end situations correctly.

Step 2: Create reports

Create two separate reports:

• One filtered where "This Week" is checked
• One filtered where "This Month" is checked

This way, only closed claims from this week or this month will appear.

Step 3: Add Report Summary fields

In each report, use the summary section to:

• Count the number of rows with a Close Date (this gives you the number of closed claims)
• Sum the Account Balance column (this gives you the total balance of those claims)

Step 4: Add reports to a dashboard

You can display the summaries or the full report using widgets in a dashboard for easy tracking at the end of each week or month.

https://app.smartsheet.com/b/publish?EQBCT=35b52ff612e84ad1b127e5412c806f9f