Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Still Looking for an IF function that does nothing to the cell if False
I asked about this back in April and got a few suggestions but nothing that really worked for me so I'll try again.
I keep track of the hours charged to around 35 projects by people in a dozen functional areas on a weekly basis and send the monthly total into our finance team for their revenue recognition process. I use links in Smartsheet to take the hours entered on project timesheets through a number of subsequent sheets that finally ends up with a total on each unique project tracking Smartsheet for a given project with the hours per function for that week via a dozen incoming links. Today I have to manually copy and paste the last week's hours into the proper week on each project tracking sheet. That weekly data, for each week the project is active, needs to stay there unchanged for the life of the project.
Here's what I would like to put in cell Design3, for example: IF(conditions, Design54,Design3) where Design3 stores the hours for a particular week for the design activities on the project and they come linked into the sheet in cell Design54. Of course, this results in a circular reference. The conditions would include matching the dates for the weeks and a central trigger for updating the current week being processed.
If the IF function would allow the "value if false" to be do nothing, essentially leave the cell unchanged (not make it blank with "" thus erasing the current value in the cell), I could trigger the weekly update from a central sheet link and have all 35 projects automatically update the current week's data. Excel allows this to work without a problem if you turn off iteration or tell it to ignore circular references (not sure about the second way). It's not a big deal but it would save me about 15 minutes per week of tedious work of opening a sheet, copying a dozen cells and pasting them into the right row.
I could probably get around this by rearchitecting the whole system but there's no time for that.