question - i have a worksheet in my excel wor...

[Segolene_Sansom]

question - i have a worksheet in my excel workbook that does not get modified by appsheet but that does pull data from appsheet modifiable sheets. It has tables with arrays in it ("{formula}"). My problem is that anytime i make a modification to the excel via appsheet the array part of the formula disappears (“formula”) and so for my report i have to go back in and reapply it to all the affected cells.

Does anyone know why this is happening or how i can get it to stop erasing my arrays? TIA

PS: I have found a workaround with some vba code to put the arrays back in but only works with formulas under 255characters.

[Phil]

@Segolene_Sansom

I can investigate if you provide: 1. Your account id from the Account pane. 2. The app name 3. The table name 4. The field name 5. The exact steps to reproduce the problem.

I am not sure I fully followed you explanation above. Any additional explanation you can provide might help me also.

[Segolene_Sansom]

@Philip_Garrett_Appsh So within the excel workbook for the app are sheets that do not get used in appsheet but are purely there for reporting purposes. Whenever any change is made within the appsheet app to the workbook these sheets also get modified for some reason and the formulas in the cells go from being array formulas to just plain formulas. it has happened in a couple workbooks and apps. if you’d like to have a look at this one: Acc ID:528453 App Name: Condition Report - Equipment Count I’m happy to provide the excel as well that goes with it to show where this happens.

[Phil]

@Segolene_Sansom

You are using an Excel workbook stored on Dropbox. Each Excel workbook resides in its own file on Dropbox. When you do an add, update, or delete to any worksheet in the workbook, we must to write the entire workbook back to Dropbox.

It would be more efficient if you could put your updatable worksheets in one workbook and your non-updatable worksheets in another workbook. That would avoid the need to write the non-updatable worksheet at all. I suspect this will cure the lost formula problem too.

Is it possible to split your worksheets between two workbooks?

[Segolene_Sansom]

@Philip_Garrett_Appsh i’ll have a look at doing that then.

just for personal understanding though, do you know why only the arrays disappear and nothing else - is it an Appsheet weakness or something on excel’s part - just trying to understand the behind the scenes. Thanks for your help though

[Phil]

@Segolene_Sansom

I am not entirely certain. We use a third party library to read and write Excel workbooks. There have been problems in the past with that library when writing .xlsm files. It appears you are using .xlsm files.

[Segolene_Sansom]

ok cool thanks for the explanation!