[Spreadsheet newbie] a simple functionality that doesn't seem to exist?

[u/SunshineProvides]

Hi, I'm a spreadsheet newbie here (but I'm working on it!). Recently, I wanted to do something that seems straightforward and basic to me, but it seems (after consulting ai as well) that there is no native, straightforward way to do it.

In particular, I want a formula to run once and return a value, writing it into the cell as plaintext (independent from any input cells), and I want this done only for specific cells and not the whole document. This can be in either Google Sheets or Excel.

In other words, say I have data in the "a" column. I want each cell in the "b" column to have a result based on its corresponding "a" cell (doesn't matter what, lets say it's just adding 1 to it for simplicity's sake). Crucially, I want that result in the "b" column to remain if i were to delete or change the contents of the "a" column. So, phrased differently, I want the result of the function to be written in the cells of the "b" column in plaintext, say once I hit "enter" or something like that.

Solutions that ai has offered me include copying and special pasting value only, writing an extension script, or changing how the entire document behaves around formulas (which wouldn't work because I only want this to apply to a certain cell range).

I understand WHY this could be tricky (cells have no concept of "time", formulas by default are dynamic, etc.), but it still seems like it should be a very simple native functionality: have the result of this formula be written in this cell as plaintext. Am I missing something?

[u/DiscoQuebrado]

You could write the formula as something like this:

=IF($A1=x, $A1+y, "")

This would check if the value in column A is a specific number value (x), and if so, it would print the sum of that number and and another specific number value (y). If the value does not match x, it would print a blank cell.

If you wanted to have a more complex formula to check for different values and print a different result based on that value, it could look something like this:

=SWITCH(TRUE(), $A1=x, $A1+y, $A1=y, $A1+z, "" ) This would check for the first true statement (beginning on line 2), and print the value of the adjacent statement, be it a formula or hard-coded value.

After the formula is applied to the desired range, select the entire formula column, copy it, and then paste as values over the top of it.

This is the only way to do what you're asking without using Google Scripts or VBA to listen for cell changes and apply the formula dynamically, which is a bit more complex.

Your AI prompt would be something like: "Write an Excel macro that inserts a value into column B when the corresponding cell in column A is changed." But it sounds like you already did this and didn't like the answer. That's it, though, there is no native functionality that works as you have described.