How A2 becomes smart and gives colleague F. the work for lookup and conversion.

Many users now use Google spreadsheets for collaborative work with team members and customers. Many users often copy the same content from left to right, from top to bottom. This will need time. If changes are made, the overview of the remaining changes is quickly lost. So why not make changes in one place and reuse them with the functional possibilities of applications? Let’s go and see how colleague Formula reuses the table cell content of A2 twice & smart.

Use case: Header for simple Orientation of Weekly Team Report

Once a week, an overview of running projects is given, helps in the recognition of problems and solution-oriented communication, if at a glance everyone is in the picture. In addition to pure text, here highlightings of essential text passages help. In our example, the calendar week is bold and the associated date range is set to a medium gray. Only the calendar week – 23 – and the year change – 2017 – is to be edited in the table cell. Start and end dates are automatically calculated one cell lower. Another Report worksheet is generated for each Report week. With Copy & Paste, the styling and formulas that have been once created can be simply reused.

The Google Sheet …

Screenshot 2017-06-16 22.21.24

For guidance purposes, Google Docs typical rows and column identifiers have slipped into the right and bottom pane for WebPublishing mode. The addressing of contents, rows, cells, and calculations is thus obtained. The first line (A) contains recorded text for the overview and easier comprehension. The second column (B) the respective calculation. The third column (C) the result. The respective calculations are first performed line by line. In lines 12 and 13, the six partial results for the desired representation of the start and end date are distributed in a cell-by-cell manner. Content and calculations can be copied and copied from the WebPublishing mode to copy and paste into your own Google tables.


Some colleague Formula background …

… sorted by Row

  1. content / code / result
  2. Google Tables allows the REGEX function. The benefit is that without other usual functions such as LEFT, MID, RIGHT, input parts can be flexibly recognized by text and number.
  3. The SPLIT function distributes content by a separator, in columns, and within a row.
  4. The backward calculation over the calendar week and the year takes „+1“ at the end of the formula a calendar week change on Monday.
  5. With the function LEFT, the standard conversion and display of a date are canceled. The first 6 characters are used to get the date from the weekday and month.
  6. For the end date of the week, six days are added to the ascertained start date by addition.
  7. Overwrites and uses the calculated date as text, as described in line 5 above.
  8. Matches the results of 5th and 7th together. In between, the text string „-“ is inserted by calculation.

… do you want more code practice stuff? Feel free to send me a line by e-mail.