Fixed value in calculated cell at Submit

Ask for Help
1

Hi,

So I have this situation:

Sheet2_colC = Sheet2_colA * Sheet2_colB
Sheet2_colA → input using app
Sheet2_colB = google sheets array {Sheet1_B2 + Sheet1_B3 + Sheet1_B4}

I have a form for input to Sheet2_colA. Everything else is automatically calculated using google sheets array formula. However, Sheet1_B2-B4 data is sometimes changed (not very often), so previous values for Sheet2_colC change, and I would like them to be fixed once Sheet2_colA is input.
What I would like to do is have value for Sheet2_colB be automatically collected based on a formula during Submit. That value would then remain fixed and there would be no array formula for Sheet2_colB.

Does this make sense? Maybe there is some other way of doing this?

2

It sounds like you could do that in Glide with a math column. Simply put what is your goal?

1 Like
3

If I use 3 “single value” columns to get cells Sheet1_B1-B3 and then use “Math column” to get Sheet2_colB and another to get Sheet2_colC I will get the correct result, BUT those values will change if I change Sheet1_B1-B3.
What I want is to have a sort of snapshot of values from Sheet1_B1-B3 so that when Sheet2_colC is calculated it NEVER changes, regardless of what I do with other cells.

4

Sounds like a Custom Form might be the way forward here.
If you use a Custom Form, you can pre-calculate those values before the form is submitted, and then write them as static values (and trash your spreadsheet formulas).

5

I made a simple example: Glide sheets example
Ideally Column B would NOT be an array formula but calculated on Submit and then “fixed” regardless of Cells in Sheet1.

@Darren_Murphy
Tnx, do you have any links/hints on how to do Custom Form?

6

The sample App in the post below is very old, but the general concept remains the same.

1 Like