To change the value of another cell

Williamgh

I've searched this subject for a couple days now and I have seen this asked a few times but with their own request with specific technicalities like percentage of completion and such. Many answers include workflow or changing the cell in question to a formula which don't appear to be the answer here.

My interest in this is to modify a numerical value in a column by both manual input of that cell and with a function of another cell.

The column in question is my quantity of inventory where we can manually input how many of something we have. The trick that I'm trying to accomplish is to be able to manipulate the quantity remotely. For example, if I sell 3 widgets that include a number of parts in that column, I have other columns that tabulate this quantity (helper) and I need to subtract 3 from each of those items in inventory.

I'm aware on how to change cell values with functions like sumif and reference other sheets with vlookup but I have yet to find a method to manipulate another column's values, whether by performing the subtraction on the fly or to copy/move the value from one column to another.

Side note, I have an individual plan and the Business plan is entirely way too expensive. So I do not have access to copy and move features, which I'm not even sure are appropriate for this. If there is a single user Business account option were available for around $400 max, then I could see how I might be able to make better use but I may have to opt for some ERP system that costs about as much as the individual plan.

Enlighten me.

Have a fantastic week! I hope you can help.

William

Andrée Starå

Hi William,

Have you looked at and tested Zapier?

It could probably be an excellent option for this scenario. Is that an option for you?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Williamgh

Andree,

I definitely can appreciate the fact that you take a moment to reply to people but your answers always come out as non-answers. And to add to it, you make that comment of having a fantastic week, as if your work is done here and you don't expect to see them for the rest of the week. I know this isn't much for positive feedback (I'm aiming for constructive criticism), but maybe you can tweak your replies to something more than, "did you think about changing it to a formula? Maybe that is an option for you."

Now that I burned down any likely chance of getting help here, I was on the phone with sales support and I briefly described this scenario and the lady was very confident that this sounded like something SS was capable of doing. She said you guys would have an answer.

So, if this capability is NOT actually available, I would LOVE to hear a technical explanation of the limitations or an option or 2 of something else I can do with SS. I was expecting a helper column that could be un-hidden, perform the deduction calculation and manually copy the column over the inventory column. Its a total lay approach but better than sending me to another product wishing me well for a week.

Sincerely,

William

Williamgh

And Zapier is a social networking workflow system that doesn't appear to have capabilities to change data in a SS table automatically. I'm not sure where your suggestion was going. But I'm not interested in Zapier.

Andrée Starå

 @Williamgh 

Happy to help! Please see my answers/comments below.

I definitely can appreciate the fact that you take a moment to reply to people but your answers always come out as non-answers. Ok? I don't agree. And to add to it, you make that comment of having a fantastic week, as if your work is done here and you don't expect to see them for the rest of the week. I'm just trying to be kind and helpful, and It's in no way a sign of that my work is done. It could be done, but I don't know until you answer me and say that it's an option or not. If it's not an option, I'd continue and try to help to find another solution. I know this isn't much for positive feedback (I'm aiming for constructive criticism), but maybe you can tweak your replies to something more than, "did you think about changing it to a formula? Maybe that is an option for you." I'm always open to constructive criticism. I base my answers on the information available in your post, and I might have misunderstood something, and maybe the answer is a real option, but not in your use-case, and that is why I ask if it's an option for you.

Now that I burned down any likely chance of getting help here, I was on the phone with sales support and I briefly described this scenario and the lady was very confident that this sounded like something SS was capable of doing. She said you guys would have an answer. Ok, that is excellent. That is what I'm trying to help you with.

So, if this capability is NOT actually available, I would LOVE to hear a technical explanation of the limitations or an option or 2 of something else I can do with SS. I was expecting a helper column that could be un-hidden, perform the deduction calculation and manually copy the column over the inventory column. Its a total lay approach but better than sending me to another product wishing me well for a week.

It's not possible in Smartsheet in the same cell because as soon as you change a cell with a formula manually, the formula will be deleted, so we would need to structure a solution with so-called helper columns.

As an example, you'd need one for the manual input and another one with the formula to sum the quantity and then another one showing the result. You can, of course, also copy the values manually.  (I didn't answer that because I got the impression that you already knew or thought about that as a solution). Make sense?

Possible workarounds.

• Copy the row(s) to another sheet and then use VLOOKOUP or a combination of INDEX/MATCH, but that's not an option because you're in the individual plan.
• Zapier.

And Zapier is a social networking workflow system that doesn't appear to have capabilities to change data in a SS table automatically. I'm not sure where your suggestion was going. But I'm not interested in Zapier. Zapier isn't a Social Networking Workflow System. It's a Workflow Automation System.

Here's the description from Zapiers homepage.

https://zapier.com

Connect your apps and automate workflows

Easy automation for busy people. Zapier moves info between your web apps automatically, so you can focus on your most important work.

I hope that helps and clarifies things!

Please let me know if I misunderstood anything and if you can share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Be safe and have a fantastic weekend!

Best,

Andrée

Paul Newcome

I have a Zapier workflow set up to automate the TODAY() function in Smartsheet. Runs once a day at a set time and has absolutely nothing to do with any social media platform whatsoever.

Having said that...

I would suggest using a formula and having somewhere else that you can manually enter. For example, you could have a form set up that will track whether you are adding or subtracting an amount, the actual amount, and the date submitted. Then you can adjust your current formulas to take this information into account.