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.

Find more posts tagged with

Finance

Formulas

Services

Comments

BobbyLite

Instead of referencing Design3 could you reference another cell that contains the value that would have been in Design3? So rather than value_if_false = do nothing it would be value_if_false = show value from this cell. Maybe add a new column (even hide it later) that contains all the values that would have been in Design, and use that column as the value_if_false

Jim Hook

All that does is move the problem to another place. In order to have the true_if_false value in another location I would have to go put it there sometime manually so I could reference it. I've thought about that, and it would work, but it still requires a manual copy/paste sometime each week--essentially what I do today and am trying to get away from.

Vyom Upadhya

Hi Jim,

I am new to SmartSheet so unfortunately can't help you but I am curious on how you got to the 35 projects multi-user tracking in the first place. Please see my discussion here: https://community.smartsheet.com/discussion/how-easily-capture-actual-time-spent-task-smartsheet for details.

Would appreciate your help!

Thanks,

Vyom

Patrick Lawler

Hi Jim,

I believe the scenario could be solved using Azuqua, an integration partner of Smartsheet. I work for Azuqua and the scenario you mentioned is one of many scenarios that we enable inside of Smartsheet.

Using Azuqua you could set a monitor on the sheets where updates are made and based on a particular update you could design a workflow that would make updates, but also only make those updates if the value was "true". Essentially, if the value is false the workflow would stop and not do anything, which if I read correctly is the main concern from your situation above.

By setting the trigger on the central sheet you would have the ability based on that one trigger to update all 35 other sheets and also respect the value if it were false to prevent updates from happening.

If you want to learn more about Azuqua please visit our listing on Smartsheet:

https://www.smartsheet.com/apps/azuqua

Please feel free to follow up with me if you have any questions or would like to discuss more in depth.

Best,

Patrick

Jim Hook

Thanks for the suggestion Patrick. I'll check Azuqua out and see what else it does that might justify the incremental expense.