Calcapp Creator can’t yet import spreadsheets directly, meaning that you need to convert them manually to apps. We have been converting quite a few spreadsheets since we announced that we’re willing to do the work for you, and we’d like to share a technique we learned along the way.
Spreadsheets and apps differ in a few fundamental respects, the primary one being the user interface. Creating a user interface for your app is usually the fun part – you get to lay out intuitive forms with drop-down menus, date and time pickers and switches, arrange them in an intuitive progression of screens and use formulas to determine at what point certain fields should become visible. All the while, you can preview your app on your phone, tablet or PC to see what it will look like.
The tricky part is transferring all the spreadsheet formulas you have been laboring over, ensuring that no details are lost. Luckily, Calcapp probably supports all the functions you’re using (except for table lookup functions). The problem is that referencing cells in a spreadsheet and referencing fields in Calcapp Creator usually are done differently.
In spreadsheets, you normally reference other cells using their
positions in the spreadsheet grid. If you point and click other cells
while you’re typing formulas, your spreadsheet automatically inserts
these position-based references. With default settings, a
position-based reference to the cell in the first column in the
second row reads A2 (or A$2 or $A$2). As a result,
your formulas usually look like the following: =IF((E9 + (E11 * 120) + C14)
<= 300, 300, (E9 + (E11 * 120)) + C14).
When you’re building an app with Calcapp Creator, you reference
fields by name and not by their positions. The aforementioned formula
converted to work with Calcapp Creator might look like this:
=IF((SubtotalBefore + (Hours *
120) + Cost) <= 300, 300, (SubtotalBefore + (Hours * 120)) +
Cost). While that is much more readable, doing the conversion
takes a lot of work if done manually.
(You can actually reference cells by name in spreadsheets too, and we actually recommend going that route to ensure that your spreadsheet formulas are easy to read and modify. Here’s how it works in Excel. Most spreadsheets are written with position-based references, though.)
Here’s an easy trick that will enable you to copy and paste formulas
from your spreadsheet to Calcapp Creator without having to change
them: simply name your fields so that they match the position-based
cell references in your spreadsheet. If you enter the caption “Cost”
for a field, its field name will automatically be set to Cost. That’s just a
default, though, and you can easily change the name in the name box:
Following the example above, the cell holding the cost in the spreadsheet is accessed using the reference C14. Simply rename your cost field “C14” by typing “C14” into the name box. If you do the same with all your fields, ensuring that their names match the references used in the original spreadsheet, you can simply copy and paste the formulas directly from your spreadsheet to Calcapp Creator and everything will work as expected.
The only problem is that your formulas in Calcapp Creator will be harder to read and maintain now that they use unintuitive names. The good news is that you can easily have Calcapp Creator rewrite them so that they use intuitive names. When you rename a field in Calcapp Creator, all formulas are updated automatically. Even better, if you click the name box (which causes the entire name to be selected) and press the backspace or delete key, the name will be replaced with one based on the caption you entered. That means that you don’t even need to manually assign intuitive names to your fields, just delete the position-based names you entered earlier for all fields and Calcapp Creator will take care of giving your fields easy-to-read names and will update all formulas.
Do you want to share a tip with other Calcapp users through this blog? Let us know!