Formulas does not work in appsheet , but in thunkable x

Please provide a link to the GoogleSheet.

Although i can see errors in the shared file, none of the the sheet names match anything you have described above.

I suggest you take another look at your problem and let us know once you have provided the correct file and the correct information.

I cannot see which cells have those formulas, and the file is locked from editing.

I recommend you make a copy of the file, and use the copy for your own purposes, then make this shared file shared with "Anyone with the link" as "Editor", then tell us which sheet and which cells have which formulas.

Please refer to columns by their letter, it is the standard that everyone can understand without having to think which column you are talking about every time you mention a number.

For "Gebruik", you can empty the column D of all data and formulas and values, and just put this into cell D1:

Please Login or Register  to view this content.

For column H, there is only 1 formula, and this is in cell H78, so it is not clear what you are trying to do.

Must I not use formula in Column 3 "Beskikbaar"

Column C "Beskikbaar" is data, and there is no source for it, and so it is the source, so there is no formula for column C, beside which you did ask for formulas for columns D and H.

Why there is only 1 showing formula in H is that I use this formula, but then with my appscript it delete it and copy it to I, as i need I not to have a formula for this to work to only show the usage of that day

when the formula is in H, id did not work for what I was using it for, it dit clash with one of my other formulas

The way we can get the best solution for you is if you can explain what kind or result you are expecting, for example is the formula in cell H78 giving the correct result? and if it is correct, can you explain why it does not yet match values in column I? maybe H78 is designed to go to I79 ?


When i seen this problem, i think that maybe there is better way to get the results you need without as much issues.

[EDIT]
So that we can work in an editable verion, i created this sample file for developing the solutions:
https://docs.google.com/spreadsheets...t#gid=18815692

Please see all the green cells on each tab/sheet, for modifications to your original formulas, that give the smae results with cleaner formulas.


I hope we can help you get best results, so please let us know.

When I add in "E" and "F" it must then it must also update "C" with the previous row of "C" (151.6) and the current row of "F" (377) and add them together (528.6), otherwise when you add new info in "C" in next line it does not show correct.

Please give clear explanation of which cells should have what values.

For example, in the sample, should C134 = 528.6 ?
or is it C135?

"I" did use a script to copy "H" to "I", there were a problem that it did not work and change the value of "H" when adding new info into new row, so we tried a script to fix this problem. Script did do the following.
1. copy "H" to "I"
2. Clean "H"
3. Make copy in "I" not to show formula and just as normal text. I think that thunkable x did not work with this, that why we did use the script.

If you take a step back, and start with the data that is imported from thunkable, then we can start creating formulas in other columns, and not be trying to work with "circular references" and the like.

I did make them green (add them together) and red(not working ) Purple(must show data)

There is no formula for adding C135, and D135 only looks at the row above.

For a different result you need to give clear explanation of exactly which cells should have what values.


ALSO, because it seems you have interspersed columns from thukable with coluns that do post-processing, it would be helpful it you tell us exactly which columns are from thunkable, and which colums you have added after thunkable, and explain why the columns are mixed instead of all thunkable columns followed by all formula columns.

Originally Posted by janmorris

Please give clear explanation of which cells should have what values.

For example, in the sample, should C134 = 528.6 ?
or is it C135?

The value in row 134, column C should display 528.6. If I subsequently input the daily usage into row 135, column C, it should then reflect the total amount I've entered. Column D should indicate the quantity I've utilized. The only instance when the value in column C will alter without my direct input is when I enter data into columns E and F

Originally Posted by janmorris

If you take a step back, and start with the data that is imported from thunkable, then we can start creating formulas in other columns, and not be trying to work with "circular references" and the like.

C =INDIRECT("R"&ROW()-1&"C3",FALSE)+INDIRECT("R"&ROW()&"C6",FALSE)
To summarize, the formula calculates the sum of two cells: one in the third column of the row above the cell containing the formula, and another in the sixth column of the same row as the formula. The specific cells to be added are determined dynamically based on the row in which the formula is written.

D =INDIRECT("C"&ROW()-1)-INDIRECT("C"&ROW())=INDIRECT("C"&ROW()-1)-INDIRECT("C"&ROW())
To summarize, this formula calculates the difference between the value in the cell one row above the cell containing the formula and the value in the current cell in column C. It then checks if both of these differences are equal and returns either TRUE or FALSE. Essentially, it is comparing the differences between consecutive rows in column C to check if they are the same.

G =IFERROR(INDIRECT("R"&ROW()&"C"&COLUMN()-2,FALSE)/INDIRECT("R"&ROW()&"C"&COLUMN()-1,FALSE),"Error")
To summarize, the formula calculates the result of dividing the value in the cell two columns to the left of the current cell by the value in the cell one column to the left of the current cell. If any error occurs during this division operation, the formula will display "Error" instead of an error message. The cell references are dynamically determined based on the current row and column.

H =ROUND(INDIRECT("G" & MAX(FILTER(ROW(G:G), G:G<>"", ROW(G:G)=MAX(FILTER(ROW(G:G), G:G<>""))))) * INDIRECT("D" & ROW()), 2)
To summarize, this formula calculates the following:

It finds the last non-blank cell in column G and takes the corresponding value.
It multiplies that value with the value in the current row of column D.
The result of the multiplication is rounded to two decimal places.
The formula is dynamic and adapts based on the data in columns G and D and the row in which it is used.

Hope this helps

If column C is based on calculations, then where is the data that is supposed to come in from thunkable or wherever else?

Ideally you should raw data, and process it in other columns, NOT do processing of the imported data directly in the same columns.. that is just making the whole project convoluted and confusing.

In that case, you should add another column, and NOT try to enter data into the same column C where forulas are expected to do calculations.

Standard process would be to have your columns of raw data either from a source or manual entry, then use exrta columns to do analysis/computing based on the data.

Spreadsheets can have up to 18,278 columns. Trying to squeeze everything into as few columns as possible isnt making use of what is available.

If you want to use formulas in headers in Google Sheets, to populate the column of results, that is possible, however you can not also input static data into any cells in those columns because it will block the formula fro populating the cells.

So you need to either have columns of static data and columns for array formulas, or just continue with your current strategy and try to make it work.

Since you want to mix using formulas and scripts, this is not a simple solution, so i recommend you either work out a simple strategy that we can help with, or hire a consultant to create the formulas and scripts for the project.