An invoice created with Numbers

20 02 2014

Apple made the iWork suite for free when buying a new iOS device to improve the user experience by providing all the basic features users expect. The iWork Suite for iOS consists of

  • Keynote
    the presentation tool for creating slides and present them to an audience
  • Pages
    the text editor to write letters and more
  • Numbers
    the spreadsheet application for calculations, stats, and other stuff for which a table is the appropriate approach

This post is about an application using Numbers for creating an invoice.

Two versions …

There is a simple approach with a template showing all products in a table. The invoice then is created by deleting unwanted rows and adjusting the quantities.

Another version is more complex and allows to refer to an additional sheet where products are listed with their prices. It uses the embedded function ‘Lookup’ to retrieve the unit price from a sheet where all products are listed.

Both versions work with a control called Stepper to adjust the desired quantity for each item (see image below). I used Apple’s template ‘Invoice’ which is available after the installation of Numbers.

The simple version …

The kernel of this version is a table where all products are listed. A calculation for the total net value, the tax, and the gross amount has already been added by Apple. If you use the template just tap on the cells to see how the calculation is done.

20131122-130320.jpg

Steps to create the invoice:

  • 1 Tap on the tab ‘Invoice’ and select ‘Duplicate’ from the context menu.
    This step is necessary to not loose the template for further invoices.
  • 2 Delete unwanted rows by tapping on the table (to select the table object).
    Bars appear above the columns and to the left of the rows.

  • 3 Tap on the row in the left bar to delete it.
    The whole invoice is immediately recalculated.
  • 4 Repeat this step for all unwanted items.

Note

If product properties have to be updated select the template ‘Invoice’ and make the necessary changes for product description or unit price.

The more complex version …

An alternate version uses a separate sheet where all products are listed, the price list. Another sheet contains the template for the invoice with some rows where the desired products must be inserted by referring to the sheet ‘Products’.
This version uses two embedded features:

  • 1 the function Lookup
    to search for a product in the sheet ‘Products’ and to insert its unit price in the column ‘Unit Price’. Calculations are done by the same formulas as in version 1.
  • 2 a control called Pop-Up Menu which contains all product descriptions.
    The values for choosing a product must be added manually when creating the control. All values have to match the original product description. Otherwise the ‘Lookup’ function doesn’t return a valid unit price.
    The ‘Pop-Up Menu’ is bound to each line of the column ‘Description’.

Here are the steps to create the Stepper Control to access the unit price from the sheet Products.

  • 1 Tap on the first cell in the column ‘Description’.
  • 2 Tap on the ‘Edit’ symbol in the task bar of Numbers (top left).
  • 3 Select the tab ‘Format’ and then ‘Pop-Up Menu’.
  • 4 Create the proper values by entering the product description.
    You have to enter the exact description used in the sheet ‘Products’. Otherwise the item cannot be found.

20131122-130439.jpg

  • 5 Use the function Lookup within the cell Unit Price to refer to the unit price in the table Products.
  • 5.1 This is done by double-tapping on the cell Unit Price. The input bar of Numbers comes up.
  • 5.2 Tap on ‘Functions’ and then on ‘Reference’.
  • 5.3 Select Lookup.
    The LOOKUP function finds a match for a given search value in one collection, and then returns the value in the cell with the same relative position in a second collection.

    LOOKUP(search for, search where, resultvalues)

    •search tor:
    The value to find. Search value can contain any value.

    Select the first parameter by tapping on it.
    Select the cell Description.
    The coordinate is overtaken as the first parameter.

    •search where:
    The collection containing the values to be searched, search where can contain any values.

    Select the second parameter by tapping on it.
    Select the sheet ‘Products’.
    Select the whole column Description by tapping on the upper bar (which comes comes up when selecting the table). The coordinates of this column are overtaken as the second parameter.

    •resultvalues:
    An optional collection containing the value to be returned based on the search, result values can contain any values.

    Select the third parameter by tapping on it.
    Select the whole column Unit Price.
    The coordinates of this column are overtaken as the third parameter.

  • 5.4 Tap on ‘Finish’ (white check mark with green background).
  • 20131122-131010.jpg

    The usage …

    If you now want to create a new invoice, copy the template, enter the customer data, and compose the product list to be calculated. The latter is done by using the Stepper Controls for Description (Step 1) and Quantity (Step 2).

    20131122-131245.jpg

    The pros and cons …

    Version 1 is simple whereas version 2 is more suitable when it comes to a large number of products. The sheet ‘Products’ can also be used as a price list. Just style it with some added images of products and add the terms and conditions.

    Another benefit of the 2nd version is that the maintenance of products is centralized. With added templates for offers, delivery notes, and sales confirmation there is just one data basis, the sheet ‘Products’. If you change details of a product it will immediately be changed in all the other templates.

    Send and archive …

    Invoices can be mailed to customers as PDFs. For archiving documents it’s recommended to save a PDF on a cloud storage for which encryption is offered, in transit as well as at rest. iDriveSync for example offers client-side encryption and can be accessed via WebDAV protocol from within iWork for iOS.

    For further information about cloud storages and WebDAV access please refer to ‘Related links’ below.

    Summary …

    For small businesses Apple’s Numbers might be useful. Legislation in nearly all countries require to save invoices for a longer period e.g. in Germany it’s 10 years. Additionally it’s required to use a strictly ascending invoice number. To fulfill all the advices mentioned in the legislation can be a hard job when using Numbers for iOS.

    But as always it’s a question of accurate work and an appropriate workflow.

    Related links …

    Numbers for iOS Devices

    Numbers for the Mac

    Risky free clouds

    WebDAV basics

    Thanks for stopping by.





    If … Then … Else

    8 03 2013

    IF your device crashed THEN

    restore it from a backup

    ELSE

    use it for improving your life

    END IF

    Software developers call this an IF-Statement.

    The part marked in red is the CONDITION and the ones in blue are ACTIONS.
    The condition is always a question to the system with an unexceptional True or False for the answer. If the condition would sound like ‘IF the car is expensive THEN’ the following action will be up to a subjective assessment of the meaning of ‘expensive’ and no rigorous True or False.

    It’s definitely the most used statement in programming and all of us are faced with this procedure over and over. In real life it’s sometimes hard to pursue the right path and we need time to decide and may go another way if our first decision was wrong. The main difference between IFs in software development and in real life is that the CONDITION in real life often cannot be reduced to a Yes or No.

    For a better understanding look at this video.
    It explains some lines of programming and the result on the iPhone’s screen.

    I’m sure this article is boring for developers but can be interesting for normal users as IF-Statements can be effectively used e.g. in Apple’s spreadsheet app Numbers.

    So let’s leave the magic activities of app developers behind us and return to daily life.

    Consider the following situations …

    Mark a line in my spreadsheet to emphasize

    • it’s important
    • it’s greater than 500
    • there are less than 30 days to an event

    Unlike in grown up spreadsheet applications installed on big brothers of the iPad conditional formatting is not available in Numbers for iOS. However here is an alternative to feature information.

    • Double tap the cell where a marker should be displayed
    • Select = from the input bar
    • Tap on Functions
    • Select Logical and Information and then If
      Numbers puts a generalized If-Statement into the input bar.
      Your task is now to fill it out with referrals to cells.
    • For defining the If-expression tap on cell C3 and add =High.
    • The action for if-true is to display the marker.
      To do this tap on “abc” on the keyboard. An input bar opens. Enter the marker e.g. from the iOS keyboard Emoji.
    • Repeat the procedure for the if-false part of the expression and in the input bar enter nothing so that nothing is displayed if the priority is not High.

    20130120-144509.jpg

    Here is the formula for an If-Statement comparing a value with a given value.
    The marker is set if the contents of cell C5 is greater than 500.

    20130120-145320.jpg

    Here is the formula for an If-Statement comparing a given date with the current date using the NOW-Function which provides the current date and time of your devices clock. Additionally the a function called DATEDIF is used which provides the difference between two dates in a selectable unit (day, month, etc.).

    20130121-053105.jpg

    If you have any questions don’t hesitate to contact me via comment or the contact form on my page ‘About’.

    Thanks for visiting http://iNotes4You.com. I hope you come back soon.





    Numbering in Apple’s Numbers

    6 03 2013

    Numbers is Apple’s app for creating spreadsheets.
    It’s part of the iWork Suite for iOS.

    From my point of view it’s the far away best app for working with spreadsheets on iOS devices.
    It’s simple but yet powerful. The UI is perfect and with the last update 1.7 (2012-12-04) Apple provided a functionality called ‘LOCK’ with which you may secure objects, e.g. tables so that their position and their content cannot be changed any longer until they are unlocked.

    I want to continue my series about the usage of Apple’s Numbers.
    The task is to automatically number lines in a table starting with a given value, to extend the numbering if lines are added and renumber them if lines are moved to new positions.

    Step 01 …

    Set the start value e.g. 100 in the upper left cell. The cell is identified by A1.

    Step 02 …

    Define a formula which tells Numbers how to generate the line number for the line following the first line with the start value.

    20130119-165031.jpg

    To define a formula for a cell double tap the cell and select ‘=’ from the input bar.
    The input field is prepared to enter a formula. Tap on the cell A1 which contains the start value 100. A symbol for this cell appears in the input field. Add 1 to this parameter and tap Done on yhe right side of the input field. 101 will appear in cell B1.

    Step 03 …

    I think you agree with me that you don’t want to repeat this procedure for the next 200 lines.
    So select cell B1 and tap on Copy in the upcoming context menu.
    Select cell C1 and expand the cell marker downwards for all lines you want to be numbered.
    Tap on Paste and then on Paste formulas.

    20130119-175324.jpg

    Step 04 …

    If you add lines to the table the new lines are firstly not numbered.
    To renumber all lines just copy the first cell B1 and repeat the Paste procedure described in Step (3). This can also be done after inserting new lines or changing places of lines.

    Thanks for visiting my blog.