Formula Question

Robert Collins

Just getting back into Smartsheets as its been a while with another company. Current company is migrating to Smartsheet after running VERY manual processes (YAY!)

My current head-scratcher:

I have a need to track cable installation footages by three categories: This Week, Last Week and To Date. Currently its VERY manual as we transition to Smartsheet - add This Week totals to To Date column then moving it to the Last Week column to the next reporting period (Weekly). The Last Week totals are strictly a holding column and used to visually see last week's reported totals.

What I have been trying unsuccessfully to do is to have a formula that would take the total input into this week and have it auto-update the To Date column when a footage is entered into the This Week cell. This would need to be done for all rows as I track on a per-job basis.

I am looking for the best approach to accomplish this. I tried to do this in excel and asked in forums there as well. I was told that there is not really a formula to do that, but a button could be crewed with code to accomplish what I want. If this is the case with Smartsheet, can a form be used to input the footages and update the column? This may actually be a better route as multiple people provide the data on specific jobs, so having them submit a form may be an option.

Any ideas/assistance would be greatly appreciated.

R

Paul Newcome

Are you able to provide some screenshots for reference?

Robert Collins

Simplistic view for reference:

Note: This is still in Excel and I am wanting to be able to duplicate this in Smartsheet.

Robert Collins

Here is some additional. If you take the above as the first week of the year, it would be zeros across the board.

End of Week one:

End of Week two:

Again, the objective is to have the Footage To Date column auto add the Footage This Week total.

Paul Newcome

You would need to set up a second sheet with those same columns and then a copy row automation to copy all rows over on a certain trigger (weekly or on entry - whichever works best for you).

They you would use formulas with cross sheet references to pull in "Last Week" and "To Date".

Last Week:

=INDEX(COLLECT({Copy Sheet This Week Column}, {Copy Sheet Job Name Column}, @cell = [Job Name]@row), COUNTIFS({Copy Sheet Job Name Column}, @cell = [Job Name]@row))

To Date:

=SUMIFS({Copy Sheet This Week Column}, {Copy Sheet Job Name Column}, @cell = [Job Name]@row)

Robert Collins

Hmmm, ok, I appreciate the response. Let me play with it some and see what I mess up...I mean do! 😆

I will let you know.

Thanks again.

R

Robert Collins

ok, I rusty on formulas and have never used SUMIFS....if I follow the above (specific to To Date), would it not only copy the current field in This Week, not add it to the current total in To Date? Again, the objective is to have a running total in To Date by adding in This Week totals.

I was able to do what I want using a macro and a button in Excel, but or course, Smartsheets doesn't have macros. Below is the code I created to do what I am wanting, with the added ability to copy This Week values to Last Week values. This also is calculating four columns of data and only deals with rows 4-10 just for testing. The actual data is several hundred rows long. I just need to figure a way to accomplish this in Smartsheet because below is a macro tied to a button to execute. Could the code still work in Smartsheet, only tie the execute to a specific day/time? If so, is this on the actual data sheet or in dynamic view? Sorry for so many questions, again rusty at this.

Sub YTD_Calculate()

Range("w4:w10").Select

Selection.Copy

Range("s4").Select

Selection.PasteSpecial Paste:=xlPasteAll

Range("o4").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _

   False, Transpose:=False

Range("w4:w10").Select

Selection.ClearContents

Range("x4:x10").Select

Selection.Copy

Range("t4").Select

Selection.PasteSpecial Paste:=xlPasteAll

Range("p4").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _

   False, Transpose:=False

Range("x4:x10").Select

Selection.ClearContents

Range("y4:y10").Select

Selection.Copy

Range("u4").Select

Selection.PasteSpecial Paste:=xlPasteAll

Range("q4").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _

   False, Transpose:=False

Range("y4:y10").Select

Selection.ClearContents

Range("z4:z10").Select

Selection.Copy

Range("v4").Select

Selection.PasteSpecial Paste:=xlPasteAll

Range("r4").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _

   False, Transpose:=False

Range("z4:z10").Select

Selection.ClearContents

End Sub

Paul Newcome

Did you give it a try? If you have the copy row automation set up to copy whenever "This Week" changes, then the current data in "This Week" will be on the copy sheet and should pull through the SUMIFS. If you add to the SUMIFS the data in the "This Week" cell, you will essentially be adding it twice.

Try setting it up as described. I am sure it will work for you.

Robert Collins

I guess my question is where am I putting it? I would assume it would need to be on the actual data sheet and not in a dynamic view. Given that, physically where? In excel I did it as a macro and tied it to the button I created and placed on the same sheet with no need for a duplicate sheet to calculate and pull from. Again, its been a while since I worked in Smartsheet. I will keep researching the sumifs command and how/where to place it to try to figure this out.

I appreciate your help.

Paul Newcome

The SUMIFS would go in the To Date column.

Robert Collins

ok, I was able to get the INDEX formula to work. My first problem was that I was trying to do this in excel and COLLECT is not an Excel function - Problem #1. That resolved, that portion of what you sent works and does copy This Week totals from the Copy sheet to the Last Week totals on the Main. I will however need to look at this as it will need to be modified. I need the ability to see Last Week, This Week and To Date at the same time, so the trigger will have to be modified I think. This shouldn't be an issue to correct.

However, the SUMIFS is only copying the This Week data. This is putting the formula in the To Date column of the Main sheet.

I can play around with it now to see if I can replicate what my Excel macro is doing.

To Date:

=SUMIFS({Copy Sheet This Week Column}, {Copy Sheet Job Name Column}, @cell = [Job Name]@row)

Paul Newcome

Do you have multiple rows in the Copy Sheet for the same Job Name? Are you able to provide a screenshot of the Copy Sheet?

If you only have a single row in the copy sheet for a job name, then it will look like it is only copying because there is only one week of data in the copy sheet.

Robert Collins

Job names are unique and I have even stepped through adding data to the copy sheet This Week columns to simulate week over week changes. No other formulas in either file. I am thinking that it is with @cell, which I have also played with to no avail. I have even researched, I just cannot figure this out. So I am positive it is something simple! Also note that I modified the moving of data from This Week to Last week as I need the ability to see both on the Formula Test file. They essentially copy the data from the Data File and should have nothing to do with the To Date totals.

This is the test sheet with the formula:

This is the data file:

Paul Newcome

If you follow the steps I provided previously and use the formulas provided, you should only need to manually enter data into the "This Week" column. Last Week and To Date are both automated.

I have already tested it out and found it to be working.

Try setting it up how I suggested:

Copy Row automation triggered when [This Week] changes.

Formula in [Last Week].

Formula in [To Date].

Manually enter this week's data into the [This Week] column.

Copy row automation triggers and copies the row over to the collection sheet.

[Last Week] formula pulls in last week's entry:

=INDEX(COLLECT({Copy Sheet This Week Column}, {Copy Sheet Job Name Column}, @cell = [Job Name]@row), COUNTIFS({Copy Sheet Job Name Column}, @cell = [Job Name]@row) - 1)

[To Date] formula sums all entries from collection sheet:

=SUMIFS({Copy Sheet This Week Column}, {Copy Sheet Job Name Column}, @cell = [Job Name]@row)

Robert Collins

My problem is with the automation apparently. I am working on it as it is copying, only it is adding rows, not updating the existing row with the new value. Still working on that....

However, I guess I am thick-headed because if I follow what you are saying:

1. Automation setup on the Data sheet to copy This Week totals from the Data sheet to the Main sheet in the same column.
2. The INDEX command resides in the Main sheet and copies This Week totals on the Data sheet to the Last Week column.
3. SUMIFS command resides in the Main sheet and totals the This Week totals on the Main sheet and the To Date total on the Main Sheet and continuously increase week over week.

If this is correct, then I still have an issue as the main sheet will show the same values for both This Week and Last week. To clarify, when you run it, you get the outputs like I posted from the macro I had working in excel correct? because I am not following that in the code you provided. But again, I am rusty on Smartsheet, so sorry to be a pain.

Paul Newcome

Yes. The automation is supposed to create a new row each time. That is how we are able to add up all of the historical entries from the "this week" column to get the "to date". Then the "last week" formula searches all entries, gathers them into an array based on the unique id, then pulls the second to last entry (what was in the "this week" column last week / before your most recent manual update in the main sheet).