Google-sheets – a cut and paste approach to converting a chunk of a google spreadsheet to markdown table

The =$A$1 notation can also be automatically moved if the value in (say) A1 is changed.

The only surefire solution is to enclose all your references in INDIRECT, e.g. =INDIRECT("$A$1") or even just =INDIRECT("A1").

The difficulty with either of these solutions is where you have many of the same formula, e.g.

=A1
=A2
=A3
...
=A99

Normally, you would fill in the first =A1 and then copy-and-paste (or drag down using autofill) to fill out the consecutive formula automatically. If, however the first formula is =$A$1 or =INDIRECT("A1") then this trick won't work (in fact, this is what $ is for - to prevent it being automatically changed).

If you have many consecutive formula, one solution is:

1. Create the formula as you normally (use plain =A1 and autofill)
2. Use the replace dialog (Ctrl+H) and use "search in formulas" to wrap all =A1, =A2 ... =A99 in the =INDIRECT("A1") etc. Note you have the option to use regular expressions when doing a find-and-replace.

In an arrangement where each of N user's changes propagate to all the other users' screens, that incurs N² propagation costs (network messages, recalculations, and display updates). That grows rapidly as N grows. So if N > 3 you want to avoid it for sure.

A separate sheet per user might avoid most of the updates but the best bet is to use a separate spreadsheet document for each user.

Given that, using a separate Google Account per user might improve performance a bit by avoiding synchronized updates to some shared state (e.g. the user's document list). Also this is the normal case, unlike one account editing docs from many tablets at once, and any optimizations will favor the normal case. You'd have to measure it to be sure, but my semi-informed bet is that you won't notice the performance difference.

Giving each user a separate login account would help with tracking changes, but that might not be worth your setup work.

Idea: If all the columns are uniform within each sheet, that is, if the data is like a simple sequence of records, look into using Google Fusion Tables in place of spreadsheets. Fusion Tables scale up to very large data sets since the rows are independent of each other. You can "publish" columns from one table data to other tables.

Idea: If this is not a temporary application, consider replacing the spreadsheets with a custom implementation as a web app or native Android apps. Even then, it's good to prototype your application with spreadsheets as a way to discover what really matters to your use.