How can I conditionally update a cell but leave the current value there if the condition fails?

Jim Hook

I keep track of about 40 active projects in our company. Among other things, I keep one Smartsheet on each project that shows the hours worked by several different functional areas on a week-by-week basis. I only want to put the current week's hours into the project history row for the current week after they have been validated. I have tried to find a way to do this using an IF statement that would do the following: IF(A=B,C,do nothing), in other words if the condition A=B, is true, put the value C in the cell, else if the condition fails, leave the old value in the cell. There is a way to do this in Excel but I haven't been able to find a way in Smartsheet. Instead I have to manually open each project sheet and do a copy and paste from where the weekly data comes into the sheet (via links from employee project timesheets) into the proper row in the project history. I only have to do this once a week but I would like to make it more automatic.

 

 

PPS Solutions

Hi Jim, before exhausting the formulae route further have you considered using reports?  Create a report which lists all 40 projects' worth of data (use Today() function to filter down to "this week's" time entries) then block copy and paste the data into your sheet containing the history of weekly approved time.  If you are (I was not sure from your comment?) that you are using a single sheet per project to keep history seperate then create a report for that using the history sheets and filter for "this week's" blank rows and then use paste to get the data in.  Basically use reports..

 

Not sure if that helps?

Jim Hook

Hi Peter,

Today the data flow through my system starts with employee project timesheets which all get linked into a combined department total for each project that their department head can view. From there all the departments get consolidated on a project-by-project basis onto a master project timesheet and from there the data goes to the individual project tracking sheets. All the transfers are done with links from one sheet to the next and I rely on the structure and sequence of projects and functions to make the linking realistic (by that I mean being able to link hundreds of cells in a single operation when creating the data flow). I have tried to use reports to do this but the sequence of charges by project and function are basically random and cannot easily be sorted out in a way that would make getting them into the individual project tracking sheets practical. After getting into the individual project tracking sheets the data goes into a number of additional Smartsheets for financial analysis. Again, this is all done via links.

 

I do also keep an Excel database of all employee project charges since we began using this system in January 2014. I use a Smartsheet report, almost exactly as you stated, to extract the weekly charges from all the possible charges before exporting them to Excel and adding them to the database for pivot table analysis. This comes in handy when a supervisor asks what their employee worked on for the past year in preparation for performance evaluations, etc.

Jeremy Michels

I may have a solution to this. I track many projects the same way. All of the projects are on one sheet though. I have columns for each month that needs tracked. In you case weeks. The weeks auto fill off of a formula. The formula uses a trigger to tell them where to go. So in our case we use moths and the trigger tells it to go to one column or the other every month. If it is jauary it goes to Juanuary by enter a 1 into the trigger cooumn. IF it goes to the othe column for Jauary it is 1.1. If you do like this you can use the data to flow into the linked cells add the trigger and it will pop it in the correct cell for the month total to the top and link ot to the next report. I run about 2600 line items on one sheet for 12 very large jobs this way with out a hitch. 

One key would be to add the new rows under the parent row of the project and add a child row per person or week as you would need. In my case I have a fixed set of items per job at the start. So I copy all of my sheets to maintain the flow. As items need to be added they are added at the bottom. 

Jim Hook

Hi Jeremy,

Thanks for the inputs. I understand how your system works with the trigger and that's what I've been trying to find a way to do in my case. I'm not sure it will work with the way my sheets are structured but I'll give it some additional thought. In my case I can easily use your approach for the current and future weeks but keeping past week rows from changing is my problem since the IF formula in those cells cannot "do nothing" if the weekly dates don't match when the trigger is set. Also, in my case I would set the trigger from a central linked location and want it to automatically update all 40 project sheets without my having to open them individually. I'm not sure when Smeartsheet would do that--would it happen in the background automatically or wait until the next time I opened each sheet which would defeat the purpose.

Travis

Jim, there is a trick which can ‘open and update’ (in the background) as many sheets as you want by changing a cell in a single sheet. To do this we will use cell linking. I suggest using a checkbox because all you will have to do is check/uncheck the box, but you could realistically use almost any cell type to do this. Link a single cell out to all 40 sheets (maybe in the first row of each sheet). When you edit the source cell, all the sheets it is linked out to will update with the new information. If you use a checkbox, you will just need to check or uncheck the box each day and all the linked sheets will ‘open and update’ in the background.  

Jim Hook

Thanks Travis. I do use the method you outlined to distribute information across multiple sheets. After thinking about it for a minute I realized that Smartsheet does automatically update everything on a sheet when new cell links arrive since in some cases I have several serial links from one sheet to another to another and a change at the beginning has rippled through them all even though I am only looking at the last one.