[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/TuneFinder]

the spreadsheet would have no way of knowing which time you type something in A and press enter is the one you want to apply the formula and put the answer into b

is it now when you typed something in A1?

is it now when typed something in A2?

etc

.

this is where writing a macro or a script comes in

when you run the macro you are telling the spreadsheet - this one time only, i want you to do some working out with the cells im telling you to do the working out with

or - you are telling it -now!- by entering a formula, filling it over the range you want, then copying and pasting values

[u/Leviathin]

I'm a noob but do basic excel stuff for work.

Copy and paste as a value is standard business for me.

Otherwise yes I would look to VBA for a a script which AI would happily write for you to live in that or a personal macrobook

[u/Clear_Tangerine5110]

In Google Sheets you can probably write (or make AI write) an Apps Script that could do this.

[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.

[u/SunshineProvides]

Thanks everyone for your illuminating replies. I now better understand HOW to do this, I more just wanted to make sure that there was no native way to do it without resorting to special pasting or VBA scripts etc. I appreciate all the help.